{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import matplotlib\n",
    "import seaborn as sns\n",
    "matplotlib.rcParams['savefig.dpi'] = 144"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from static_grader import grader"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DW Miniproject\n",
    "## Introduction\n",
    "\n",
    "The objective of this miniproject is to exercise your ability to wrangle tabular data set and aggregate large data sets into meaningful summary statistics. We will be working with the same medical data used in the `pw` miniproject, but will be leveraging the power of Pandas to more efficiently represent and act on our data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Downloading the data\n",
    "\n",
    "We first need to download the data we'll be using from Amazon S3:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mkdir: cannot create directory 'dw-data': File exists\r\n"
     ]
    }
   ],
   "source": [
    "!mkdir dw-data\n",
    "!aws s3 sync s3://dataincubator-wqu/dwdata-ease/ ./dw-data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading the data\n",
    "\n",
    "Similar to the `PW` miniproject, the first step is to read in the data. The data files are stored as compressed CSV files. You can load the data into a Pandas DataFrame by making use of the `gzip` package to decompress the files and Panda's `read_csv` methods to parse the data into a DataFrame. You may want to check the Pandas documentation for parsing [CSV](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html) files for reference.\n",
    "\n",
    "For a description of the data set please, refer to the [PW miniproject](./pw.ipynb)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import gzip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B87016</td>\n",
       "      <td>213200001</td>\n",
       "      <td>MucoClear Sod Chlor 6% Inh Soln 4ml</td>\n",
       "      <td>1</td>\n",
       "      <td>38.94</td>\n",
       "      <td>36.06</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L85017</td>\n",
       "      <td>0503021C0</td>\n",
       "      <td>Aciclovir_Tab 800mg</td>\n",
       "      <td>4</td>\n",
       "      <td>13.44</td>\n",
       "      <td>12.49</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M81001</td>\n",
       "      <td>090900000</td>\n",
       "      <td>SMA_High Energy Milk Ready To Use</td>\n",
       "      <td>1</td>\n",
       "      <td>147.60</td>\n",
       "      <td>136.65</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F85063</td>\n",
       "      <td>0601011L0</td>\n",
       "      <td>Ins Humalog_100u/ml 10ml Vl</td>\n",
       "      <td>1</td>\n",
       "      <td>33.22</td>\n",
       "      <td>30.77</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B86667</td>\n",
       "      <td>1001010P0</td>\n",
       "      <td>Naproxen_Tab E/C 500mg</td>\n",
       "      <td>1</td>\n",
       "      <td>3.51</td>\n",
       "      <td>3.36</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                             bnf_name  items     nic  \\\n",
       "0   B87016  213200001  MucoClear Sod Chlor 6% Inh Soln 4ml      1   38.94   \n",
       "1   L85017  0503021C0                  Aciclovir_Tab 800mg      4   13.44   \n",
       "2   M81001  090900000    SMA_High Energy Milk Ready To Use      1  147.60   \n",
       "3   F85063  0601011L0          Ins Humalog_100u/ml 10ml Vl      1   33.22   \n",
       "4   B86667  1001010P0               Naproxen_Tab E/C 500mg      1    3.51   \n",
       "\n",
       "   act_cost  quantity  \n",
       "0     36.06        60  \n",
       "1     12.49       140  \n",
       "2    136.65     15000  \n",
       "3     30.77         2  \n",
       "4      3.36        28  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scripts_file = gzip.open('./dw-data/201701scripts_sample.csv.gz', 'r')\n",
    "    \n",
    "scripts = pd.read_csv(scripts_file)\n",
    "scripts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>P84046</td>\n",
       "      <td>0101010R0</td>\n",
       "      <td>Simeticone_Dps 21mg/2.5ml</td>\n",
       "      <td>1</td>\n",
       "      <td>3.46</td>\n",
       "      <td>3.22</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>N81623</td>\n",
       "      <td>040201030</td>\n",
       "      <td>Risperidone_Tab 500mcg</td>\n",
       "      <td>1</td>\n",
       "      <td>1.15</td>\n",
       "      <td>1.18</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>E85658</td>\n",
       "      <td>0407020A0</td>\n",
       "      <td>Fentanyl_Transdermal Patch 12mcg/hr</td>\n",
       "      <td>2</td>\n",
       "      <td>50.36</td>\n",
       "      <td>46.65</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>E82106</td>\n",
       "      <td>212300001</td>\n",
       "      <td>Sylk Vag Moist 40g Tube</td>\n",
       "      <td>2</td>\n",
       "      <td>10.32</td>\n",
       "      <td>9.58</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>L83015</td>\n",
       "      <td>0403010N0</td>\n",
       "      <td>Imipramine HCl_Tab 10mg</td>\n",
       "      <td>1</td>\n",
       "      <td>2.10</td>\n",
       "      <td>1.96</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0 practice   bnf_code                             bnf_name  items  \\\n",
       "0           0   P84046  0101010R0            Simeticone_Dps 21mg/2.5ml      1   \n",
       "1           1   N81623  040201030               Risperidone_Tab 500mcg      1   \n",
       "2           2   E85658  0407020A0  Fentanyl_Transdermal Patch 12mcg/hr      2   \n",
       "3           3   E82106  212300001              Sylk Vag Moist 40g Tube      2   \n",
       "4           4   L83015  0403010N0              Imipramine HCl_Tab 10mg      1   \n",
       "\n",
       "     nic  act_cost  quantity  \n",
       "0   3.46      3.22       200  \n",
       "1   1.15      1.18        28  \n",
       "2  50.36     46.65        20  \n",
       "3  10.32      9.58         2  \n",
       "4   2.10      1.96        56  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scripts_file_16 = gzip.open('./dw-data/201606scripts_sample.csv.gz', 'r')\n",
    "    \n",
    "scripts_16 = pd.read_csv(scripts_file_16)\n",
    "scripts_16.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>addr_1</th>\n",
       "      <th>addr_2</th>\n",
       "      <th>borough</th>\n",
       "      <th>village</th>\n",
       "      <th>post_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "      <td>THE HEALTH CENTRE</td>\n",
       "      <td>LAWSON STREET</td>\n",
       "      <td>STOCKTON ON TEES</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS18 1HU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81002</td>\n",
       "      <td>QUEENS PARK MEDICAL CENTRE</td>\n",
       "      <td>QUEENS PARK MEDICAL CTR</td>\n",
       "      <td>FARRER STREET</td>\n",
       "      <td>STOCKTON ON TEES</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS18 2AW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81003</td>\n",
       "      <td>VICTORIA MEDICAL PRACTICE</td>\n",
       "      <td>THE HEALTH CENTRE</td>\n",
       "      <td>VICTORIA ROAD</td>\n",
       "      <td>HARTLEPOOL</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS26 8DB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81004</td>\n",
       "      <td>WOODLANDS ROAD SURGERY</td>\n",
       "      <td>6 WOODLANDS ROAD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MIDDLESBROUGH</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS1 3BE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81005</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "      <td>RECTORY LANE</td>\n",
       "      <td>GUISBOROUGH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TS14 7DJ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code                        name                   addr_1         addr_2  \\\n",
       "0  A81001         THE DENSHAM SURGERY        THE HEALTH CENTRE  LAWSON STREET   \n",
       "1  A81002  QUEENS PARK MEDICAL CENTRE  QUEENS PARK MEDICAL CTR  FARRER STREET   \n",
       "2  A81003   VICTORIA MEDICAL PRACTICE        THE HEALTH CENTRE  VICTORIA ROAD   \n",
       "3  A81004      WOODLANDS ROAD SURGERY         6 WOODLANDS ROAD            NaN   \n",
       "4  A81005          SPRINGWOOD SURGERY       SPRINGWOOD SURGERY   RECTORY LANE   \n",
       "\n",
       "            borough    village post_code  \n",
       "0  STOCKTON ON TEES  CLEVELAND  TS18 1HU  \n",
       "1  STOCKTON ON TEES  CLEVELAND  TS18 2AW  \n",
       "2        HARTLEPOOL  CLEVELAND  TS26 8DB  \n",
       "3     MIDDLESBROUGH  CLEVELAND   TS1 3BE  \n",
       "4       GUISBOROUGH        NaN  TS14 7DJ  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "practices_file = gzip.open('./dw-data/practices.csv.gz', 'r')\n",
    "\n",
    "col_names=[ 'code', 'name', 'addr_1', 'addr_2', 'borough', 'village', 'post_code']\n",
    "practices = pd.read_csv(practices_file, names = col_names)\n",
    "practices.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CHEM SUB</th>\n",
       "      <th>NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0101010A0</td>\n",
       "      <td>Alexitol Sodium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0101010B0</td>\n",
       "      <td>Almasilate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0101010C0</td>\n",
       "      <td>Aluminium Hydroxide</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0101010D0</td>\n",
       "      <td>Aluminium Hydroxide With Magnesium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0101010E0</td>\n",
       "      <td>Hydrotalcite</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    CHEM SUB                                NAME\n",
       "0  0101010A0                     Alexitol Sodium\n",
       "1  0101010B0                          Almasilate\n",
       "2  0101010C0                 Aluminium Hydroxide\n",
       "3  0101010D0  Aluminium Hydroxide With Magnesium\n",
       "4  0101010E0                        Hydrotalcite"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chem = pd.read_csv(gzip.open('./dw-data/chem.csv.gz', 'r'))\n",
    "chem.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "now that we've loaded in the data, let's first replicate our results from the `PW` miniproject. Note that we are now working with a larger data set so the answers will be different than in the `PW` miniproject even if the analysis is the same."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 1: summary_statistics\n",
    "\n",
    "In the `PW` miniproject we first calculated the total, mean, standard deviation, and quartile statistics of the `'items'`, `'quantity'`', `'nic'`, and `'act_cost'` fields. To do this we had to write some functions to calculate the statistics and apply the functions to our data structure. The DataFrame has a `describe` method that will calculate most (not all) of these things for us.\n",
    "\n",
    "Submit the summary statistics to the grader as a list of tuples: [('act_cost', (total, mean, std, q25, median, q75)), ...]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B87016</td>\n",
       "      <td>213200001</td>\n",
       "      <td>MucoClear Sod Chlor 6% Inh Soln 4ml</td>\n",
       "      <td>1</td>\n",
       "      <td>38.94</td>\n",
       "      <td>36.06</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L85017</td>\n",
       "      <td>0503021C0</td>\n",
       "      <td>Aciclovir_Tab 800mg</td>\n",
       "      <td>4</td>\n",
       "      <td>13.44</td>\n",
       "      <td>12.49</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M81001</td>\n",
       "      <td>090900000</td>\n",
       "      <td>SMA_High Energy Milk Ready To Use</td>\n",
       "      <td>1</td>\n",
       "      <td>147.60</td>\n",
       "      <td>136.65</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F85063</td>\n",
       "      <td>0601011L0</td>\n",
       "      <td>Ins Humalog_100u/ml 10ml Vl</td>\n",
       "      <td>1</td>\n",
       "      <td>33.22</td>\n",
       "      <td>30.77</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B86667</td>\n",
       "      <td>1001010P0</td>\n",
       "      <td>Naproxen_Tab E/C 500mg</td>\n",
       "      <td>1</td>\n",
       "      <td>3.51</td>\n",
       "      <td>3.36</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                             bnf_name  items     nic  \\\n",
       "0   B87016  213200001  MucoClear Sod Chlor 6% Inh Soln 4ml      1   38.94   \n",
       "1   L85017  0503021C0                  Aciclovir_Tab 800mg      4   13.44   \n",
       "2   M81001  090900000    SMA_High Energy Milk Ready To Use      1  147.60   \n",
       "3   F85063  0601011L0          Ins Humalog_100u/ml 10ml Vl      1   33.22   \n",
       "4   B86667  1001010P0               Naproxen_Tab E/C 500mg      1    3.51   \n",
       "\n",
       "   act_cost  quantity  \n",
       "0     36.06        60  \n",
       "1     12.49       140  \n",
       "2    136.65     15000  \n",
       "3     30.77         2  \n",
       "4      3.36        28  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scripts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>sum</th>\n",
       "      <td>8982877</td>\n",
       "      <td>72490066.94</td>\n",
       "      <td>67440722.67</td>\n",
       "      <td>725345509</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       items          nic     act_cost   quantity\n",
       "sum  8982877  72490066.94  67440722.67  725345509"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scripts_sum = scripts[['items','nic','act_cost','quantity']].agg(['sum'])\n",
    "scripts_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1.001634e+06</td>\n",
       "      <td>1.001634e+06</td>\n",
       "      <td>1.001634e+06</td>\n",
       "      <td>1.001634e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>8.968223e+00</td>\n",
       "      <td>7.237181e+01</td>\n",
       "      <td>6.733070e+01</td>\n",
       "      <td>7.241622e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.966303e+01</td>\n",
       "      <td>1.913729e+02</td>\n",
       "      <td>1.774985e+02</td>\n",
       "      <td>3.882238e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.000000e-02</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>7.840000e+00</td>\n",
       "      <td>7.350000e+00</td>\n",
       "      <td>2.800000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>2.256000e+01</td>\n",
       "      <td>2.113000e+01</td>\n",
       "      <td>1.000000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>6.000000e+00</td>\n",
       "      <td>6.440000e+01</td>\n",
       "      <td>5.993000e+01</td>\n",
       "      <td>3.360000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>3.333000e+03</td>\n",
       "      <td>1.963500e+04</td>\n",
       "      <td>1.817733e+04</td>\n",
       "      <td>6.526240e+05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              items           nic      act_cost      quantity\n",
       "count  1.001634e+06  1.001634e+06  1.001634e+06  1.001634e+06\n",
       "mean   8.968223e+00  7.237181e+01  6.733070e+01  7.241622e+02\n",
       "std    2.966303e+01  1.913729e+02  1.774985e+02  3.882238e+03\n",
       "min    1.000000e+00  0.000000e+00  1.000000e-02  0.000000e+00\n",
       "25%    1.000000e+00  7.840000e+00  7.350000e+00  2.800000e+01\n",
       "50%    2.000000e+00  2.256000e+01  2.113000e+01  1.000000e+02\n",
       "75%    6.000000e+00  6.440000e+01  5.993000e+01  3.360000e+02\n",
       "max    3.333000e+03  1.963500e+04  1.817733e+04  6.526240e+05"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scripts_des = scripts.describe()\n",
    "scripts_des"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>sum</th>\n",
       "      <td>8.982877e+06</td>\n",
       "      <td>7.249007e+07</td>\n",
       "      <td>6.744072e+07</td>\n",
       "      <td>7.253455e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>8.968223e+00</td>\n",
       "      <td>7.237181e+01</td>\n",
       "      <td>6.733070e+01</td>\n",
       "      <td>7.241622e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.966303e+01</td>\n",
       "      <td>1.913729e+02</td>\n",
       "      <td>1.774985e+02</td>\n",
       "      <td>3.882238e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>7.840000e+00</td>\n",
       "      <td>7.350000e+00</td>\n",
       "      <td>2.800000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>2.256000e+01</td>\n",
       "      <td>2.113000e+01</td>\n",
       "      <td>1.000000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>6.000000e+00</td>\n",
       "      <td>6.440000e+01</td>\n",
       "      <td>5.993000e+01</td>\n",
       "      <td>3.360000e+02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             items           nic      act_cost      quantity\n",
       "sum   8.982877e+06  7.249007e+07  6.744072e+07  7.253455e+08\n",
       "mean  8.968223e+00  7.237181e+01  6.733070e+01  7.241622e+02\n",
       "std   2.966303e+01  1.913729e+02  1.774985e+02  3.882238e+03\n",
       "25%   1.000000e+00  7.840000e+00  7.350000e+00  2.800000e+01\n",
       "50%   2.000000e+00  2.256000e+01  2.113000e+01  1.000000e+02\n",
       "75%   6.000000e+00  6.440000e+01  5.993000e+01  3.360000e+02"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scripts_output = pd.concat([scripts_sum, scripts_des.drop(['count','min','max'])])\n",
    "scripts_output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "act_cost_l = []\n",
    "for i in scripts_output['act_cost']:\n",
    "    act_cost_l.append(i)\n",
    "    \n",
    "nic_l = []\n",
    "for i in scripts_output['nic']:\n",
    "    nic_l.append(i)\n",
    "\n",
    "items_l = []\n",
    "for i in scripts_output['items']:\n",
    "    items_l.append(i)\n",
    "    \n",
    "quantity_l = []\n",
    "for i in scripts_output['quantity']:\n",
    "    quantity_l.append(i)\n",
    "\n",
    "#print(output_list_of_tuples)   \n",
    "output_list = [('items', tuple(items_l)), ('quantity', tuple(quantity_l)), ('nic', tuple(nic_l)),('act_cost', tuple(act_cost_l))]\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def summary_stats():\n",
    "    return output_list\n",
    "    #return [('items', (1,) * 6), ('quantity', (1,) * 6), ('nic', (1,) * 6), ('act_cost', (1,) * 6)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score('dw__summary_statistics', summary_stats)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 2: most_common_item\n",
    "\n",
    "We can also easily compute summary statistics on groups within the data. In the `pw` miniproject we had to explicitly construct the groups based on the values of a particular field. Pandas will handle that for us via the `groupby` method. This process is [detailed in the Pandas documentation](https://pandas.pydata.org/pandas-docs/stable/groupby.html).\n",
    "\n",
    "Use `groupby` to calculate the total number of items dispensed for each `'bnf_name'`. Find the item with the highest total and return the result as `(bnf_name, total)`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B87016</td>\n",
       "      <td>213200001</td>\n",
       "      <td>MucoClear Sod Chlor 6% Inh Soln 4ml</td>\n",
       "      <td>1</td>\n",
       "      <td>38.94</td>\n",
       "      <td>36.06</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L85017</td>\n",
       "      <td>0503021C0</td>\n",
       "      <td>Aciclovir_Tab 800mg</td>\n",
       "      <td>4</td>\n",
       "      <td>13.44</td>\n",
       "      <td>12.49</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M81001</td>\n",
       "      <td>090900000</td>\n",
       "      <td>SMA_High Energy Milk Ready To Use</td>\n",
       "      <td>1</td>\n",
       "      <td>147.60</td>\n",
       "      <td>136.65</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F85063</td>\n",
       "      <td>0601011L0</td>\n",
       "      <td>Ins Humalog_100u/ml 10ml Vl</td>\n",
       "      <td>1</td>\n",
       "      <td>33.22</td>\n",
       "      <td>30.77</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B86667</td>\n",
       "      <td>1001010P0</td>\n",
       "      <td>Naproxen_Tab E/C 500mg</td>\n",
       "      <td>1</td>\n",
       "      <td>3.51</td>\n",
       "      <td>3.36</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                             bnf_name  items     nic  \\\n",
       "0   B87016  213200001  MucoClear Sod Chlor 6% Inh Soln 4ml      1   38.94   \n",
       "1   L85017  0503021C0                  Aciclovir_Tab 800mg      4   13.44   \n",
       "2   M81001  090900000    SMA_High Energy Milk Ready To Use      1  147.60   \n",
       "3   F85063  0601011L0          Ins Humalog_100u/ml 10ml Vl      1   33.22   \n",
       "4   B86667  1001010P0               Naproxen_Tab E/C 500mg      1    3.51   \n",
       "\n",
       "   act_cost  quantity  \n",
       "0     36.06        60  \n",
       "1     12.49       140  \n",
       "2    136.65     15000  \n",
       "3     30.77         2  \n",
       "4      3.36        28  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scripts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bnf_name\n",
       "365 Film 4cm x 5cm VP Adh Film Dress          1\n",
       "365 IV Transpt IV 10cm x 12cm VP Adh Fil      1\n",
       "365 Non Adherent 10cm x 10cm Pfa Plas Fa      1\n",
       "365 Non Woven Island 10cm x 10cm Adh Dre      2\n",
       "3M Micropore Silicone 2.5cm x 5m Surg Ad     14\n",
       "3M Micropore Silicone 5cm x 5m Surg Adh       7\n",
       "3m Health Care_Cavilon Durable Barrier C    913\n",
       "3m Health Care_Cavilon No Sting 1ml Barr    209\n",
       "3m Health Care_Cavilon No Sting 3ml Barr     88\n",
       "3m Health Care_Cavilon No Sting Barrier     466\n",
       "4Head_Top Headache Relief Stick               4\n",
       "A & P_Infants Pdrs                            4\n",
       "A.S Saliva Orthana Spy 50ml (App)           116\n",
       "A.S Saliva Orthana Spy Refill 500ml (App      6\n",
       "A2A Spacer                                   30\n",
       "A2A Spacer + Sml/Med Mask                    73\n",
       "AAA_Sore Throat A/Spy 7.5g                    3\n",
       "AMI_Corsinel Suportx Ab Tube Wte ExLge (      1\n",
       "AMI_Corsinel Suportx Ab Tube Wte Lge (94      1\n",
       "AMI_Corsinel Suportx Ab Tube Wte Med (86      2\n",
       "AMI_Corsinel Suportx Easy Panel Belt Wte      1\n",
       "AMI_Corsinel Suportx M/M Fle                  1\n",
       "AMI_Corsinel Suportx StomaSafe Plus Belt      1\n",
       "AMI_Suportx Hernia Support Girdles Fle H      7\n",
       "AMI_Suportx Hernia Support Girdles Fle L     21\n",
       "AMI_Suportx Hernia Support Girdles Male       5\n",
       "AMI_Suportx Ostomy & A/Support Ladies Br      3\n",
       "AMI_Suportx Ostomy & A/Support Ladies Sh      1\n",
       "AMI_Suportx Ostomy Support Mens Shorts B      2\n",
       "AMI_Suportx Ostomy Support Mens Shorts D      2\n",
       "                                           ... \n",
       "iMEDicare_Brief XL                            1\n",
       "iMEDicare_Collection Bag Stabilizer (Hig      1\n",
       "iMEDicare_Core Supporter Brief Med            1\n",
       "iMEDicare_High Receptacle                     4\n",
       "iMEDicare_Urine Collection Bag 1200ml         3\n",
       "iMEDicare_Urine Collection Bag 2000ml         1\n",
       "iMEDicare_Urine Collection Bag 500ml          5\n",
       "imuDERM Urea Emollient                       12\n",
       "kliniderm Foam Lite Border 10cm x 10cm W      3\n",
       "kliniderm Foam Lite Border 15cm x 15cm W      1\n",
       "kliniderm Foam Lite Border 7.5cmx7.5cm W      3\n",
       "kliniderm Foam Slc 10cm x 10cm Wound Dre     15\n",
       "kliniderm Foam Slc 5cm x 5cm Wound Dress      4\n",
       "kliniderm Foam Slc Border 10cm x 10cm Wo     35\n",
       "kliniderm Foam Slc Border 10cm x 20cm Wo      3\n",
       "kliniderm Foam Slc Border 12.5cmx12.5cm       9\n",
       "kliniderm Foam Slc Border 15cm x 15cm Wo      6\n",
       "kliniderm Foam Slc Border 15cm x 20cm Wo      4\n",
       "kliniderm Foam Slc Border 7.5cm x 7.5cm      19\n",
       "kliniderm Foam Slc Sacrum Border 18cmx18      2\n",
       "kliniderm superabsorbent 10cm x 10cm Pfa     15\n",
       "kliniderm superabsorbent 10cm x 15cm Pfa     14\n",
       "kliniderm superabsorbent 20cm x 20cm Pfa     21\n",
       "kliniderm superabsorbent 20cm x 30cm Pfa     22\n",
       "nSpire PiKo-1 Stnd Range Peak Flow Meter      2\n",
       "nSpire Pocket Peak Low Range Peak Flow M      5\n",
       "nSpire Pocket Peak Stnd Range Peak Flow       3\n",
       "nSpire Rep Mthpce Stnd Size Peak Flow Me      2\n",
       "oraNurse_Toothpaste Orig (1450ppm)            4\n",
       "palmdoc (Reagent)_Strips                      8\n",
       "Name: items, Length: 13674, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scripts_gp = scripts.groupby('bnf_name')['items'].count()\n",
    "scripts_gp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bnf_name\n",
       "365 Film 4cm x 5cm VP Adh Film Dress         1\n",
       "365 IV Transpt IV 10cm x 12cm VP Adh Fil     1\n",
       "365 Non Adherent 10cm x 10cm Pfa Plas Fa     1\n",
       "365 Non Woven Island 10cm x 10cm Adh Dre     4\n",
       "3M Micropore Silicone 2.5cm x 5m Surg Ad    16\n",
       "Name: items, dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sums = scripts.groupby('bnf_name').sum()['items']\n",
    "sums.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def most_common_item():\n",
    "    sums = scripts.groupby('bnf_name').sum()['items']\n",
    "    item = (sums.idxmax(), sums.max())\n",
    "    return [item]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Omeprazole_Cap E/C 20mg', 222007)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "answer = most_common_item()\n",
    "answer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "def most_common_item():\n",
    "    return (0, 1643)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score('dw__most_common_item', most_common_item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 3: items_by_region\n",
    "\n",
    "Now let's find the most common item by post code. The post code information is in the `practices` DataFrame, and we'll need to `merge` it into the `scripts` DataFrame. Pandas provides [extensive documentation](https://pandas.pydata.org/pandas-docs/stable/merging.html) with diagrammed examples on different methods and approaches for joining data. The `merge` method is only one of many possible options.\n",
    "\n",
    "Return your results as a list of tuples `(post code, item name, amount dispensed as % of total)`. Sort your results ascending alphabetically by post code and take only results from the first 100 post codes.\n",
    "\n",
    "**NOTE:** Some practices have multiple postal codes associated with them. Use the alphabetically first postal code. Note some postal codes may have multiple `'bnf_name'` with the same prescription rate for the maximum. In this case, take the alphabetically first `'bnf_name'` (as in the PW miniproject)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>addr_1</th>\n",
       "      <th>addr_2</th>\n",
       "      <th>borough</th>\n",
       "      <th>village</th>\n",
       "      <th>post_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "      <td>THE HEALTH CENTRE</td>\n",
       "      <td>LAWSON STREET</td>\n",
       "      <td>STOCKTON ON TEES</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS18 1HU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81002</td>\n",
       "      <td>QUEENS PARK MEDICAL CENTRE</td>\n",
       "      <td>QUEENS PARK MEDICAL CTR</td>\n",
       "      <td>FARRER STREET</td>\n",
       "      <td>STOCKTON ON TEES</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS18 2AW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81003</td>\n",
       "      <td>VICTORIA MEDICAL PRACTICE</td>\n",
       "      <td>THE HEALTH CENTRE</td>\n",
       "      <td>VICTORIA ROAD</td>\n",
       "      <td>HARTLEPOOL</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS26 8DB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81004</td>\n",
       "      <td>WOODLANDS ROAD SURGERY</td>\n",
       "      <td>6 WOODLANDS ROAD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MIDDLESBROUGH</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS1 3BE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81005</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "      <td>RECTORY LANE</td>\n",
       "      <td>GUISBOROUGH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TS14 7DJ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code                        name                   addr_1         addr_2  \\\n",
       "0  A81001         THE DENSHAM SURGERY        THE HEALTH CENTRE  LAWSON STREET   \n",
       "1  A81002  QUEENS PARK MEDICAL CENTRE  QUEENS PARK MEDICAL CTR  FARRER STREET   \n",
       "2  A81003   VICTORIA MEDICAL PRACTICE        THE HEALTH CENTRE  VICTORIA ROAD   \n",
       "3  A81004      WOODLANDS ROAD SURGERY         6 WOODLANDS ROAD            NaN   \n",
       "4  A81005          SPRINGWOOD SURGERY       SPRINGWOOD SURGERY   RECTORY LANE   \n",
       "\n",
       "            borough    village post_code  \n",
       "0  STOCKTON ON TEES  CLEVELAND  TS18 1HU  \n",
       "1  STOCKTON ON TEES  CLEVELAND  TS18 2AW  \n",
       "2        HARTLEPOOL  CLEVELAND  TS26 8DB  \n",
       "3     MIDDLESBROUGH  CLEVELAND   TS1 3BE  \n",
       "4       GUISBOROUGH        NaN  TS14 7DJ  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "practices.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B87016</td>\n",
       "      <td>213200001</td>\n",
       "      <td>MucoClear Sod Chlor 6% Inh Soln 4ml</td>\n",
       "      <td>1</td>\n",
       "      <td>38.94</td>\n",
       "      <td>36.06</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L85017</td>\n",
       "      <td>0503021C0</td>\n",
       "      <td>Aciclovir_Tab 800mg</td>\n",
       "      <td>4</td>\n",
       "      <td>13.44</td>\n",
       "      <td>12.49</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M81001</td>\n",
       "      <td>090900000</td>\n",
       "      <td>SMA_High Energy Milk Ready To Use</td>\n",
       "      <td>1</td>\n",
       "      <td>147.60</td>\n",
       "      <td>136.65</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F85063</td>\n",
       "      <td>0601011L0</td>\n",
       "      <td>Ins Humalog_100u/ml 10ml Vl</td>\n",
       "      <td>1</td>\n",
       "      <td>33.22</td>\n",
       "      <td>30.77</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B86667</td>\n",
       "      <td>1001010P0</td>\n",
       "      <td>Naproxen_Tab E/C 500mg</td>\n",
       "      <td>1</td>\n",
       "      <td>3.51</td>\n",
       "      <td>3.36</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                             bnf_name  items     nic  \\\n",
       "0   B87016  213200001  MucoClear Sod Chlor 6% Inh Soln 4ml      1   38.94   \n",
       "1   L85017  0503021C0                  Aciclovir_Tab 800mg      4   13.44   \n",
       "2   M81001  090900000    SMA_High Energy Milk Ready To Use      1  147.60   \n",
       "3   F85063  0601011L0          Ins Humalog_100u/ml 10ml Vl      1   33.22   \n",
       "4   B86667  1001010P0               Naproxen_Tab E/C 500mg      1    3.51   \n",
       "\n",
       "   act_cost  quantity  \n",
       "0     36.06        60  \n",
       "1     12.49       140  \n",
       "2    136.65     15000  \n",
       "3     30.77         2  \n",
       "4      3.36        28  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scripts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>addr_1</th>\n",
       "      <th>addr_2</th>\n",
       "      <th>borough</th>\n",
       "      <th>village</th>\n",
       "      <th>post_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1896</th>\n",
       "      <td>E82060</td>\n",
       "      <td>PARKBURY HOUSE SURGERY</td>\n",
       "      <td>PARKBURY HOUSE</td>\n",
       "      <td>ST.PETERS STREET</td>\n",
       "      <td>ST.ALBANS</td>\n",
       "      <td>HERTFORDSHIRE</td>\n",
       "      <td>AL1 3HD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1871</th>\n",
       "      <td>E82031</td>\n",
       "      <td>MALTINGS SURGERY</td>\n",
       "      <td>THE MALTINGS SURGERY</td>\n",
       "      <td>8-14 VICTORIA STREET</td>\n",
       "      <td>ST ALBANS</td>\n",
       "      <td>HERTFORDSHIRE</td>\n",
       "      <td>AL1 3JB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1849</th>\n",
       "      <td>E82004</td>\n",
       "      <td>HATFIELD ROAD SURGERY</td>\n",
       "      <td>61 HATFIELD ROAD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST.ALBANS</td>\n",
       "      <td>HERTFORDSHIRE</td>\n",
       "      <td>AL1 4JE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1848</th>\n",
       "      <td>E82002</td>\n",
       "      <td>WRAFTON HOUSE SURGERY</td>\n",
       "      <td>WRAFTON HOUSE SURGERY</td>\n",
       "      <td>9/11 WELLFIELD ROAD</td>\n",
       "      <td>HATFIELD</td>\n",
       "      <td>HERTFORDSHIRE</td>\n",
       "      <td>AL10 0BS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9812</th>\n",
       "      <td>Y05146</td>\n",
       "      <td>HCT LYMPHOEDEMA AT WEST ESSEX CCG</td>\n",
       "      <td>QUEENSWAY HEALTH CENTRE</td>\n",
       "      <td>QUEENSWAY</td>\n",
       "      <td>HATFIELD</td>\n",
       "      <td>HERTFORDSHIRE</td>\n",
       "      <td>AL10 0LF</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        code                               name                   addr_1  \\\n",
       "1896  E82060             PARKBURY HOUSE SURGERY           PARKBURY HOUSE   \n",
       "1871  E82031                   MALTINGS SURGERY     THE MALTINGS SURGERY   \n",
       "1849  E82004              HATFIELD ROAD SURGERY         61 HATFIELD ROAD   \n",
       "1848  E82002              WRAFTON HOUSE SURGERY    WRAFTON HOUSE SURGERY   \n",
       "9812  Y05146  HCT LYMPHOEDEMA AT WEST ESSEX CCG  QUEENSWAY HEALTH CENTRE   \n",
       "\n",
       "                    addr_2    borough        village post_code  \n",
       "1896      ST.PETERS STREET  ST.ALBANS  HERTFORDSHIRE   AL1 3HD  \n",
       "1871  8-14 VICTORIA STREET  ST ALBANS  HERTFORDSHIRE   AL1 3JB  \n",
       "1849                   NaN  ST.ALBANS  HERTFORDSHIRE   AL1 4JE  \n",
       "1848   9/11 WELLFIELD ROAD   HATFIELD  HERTFORDSHIRE  AL10 0BS  \n",
       "9812             QUEENSWAY   HATFIELD  HERTFORDSHIRE  AL10 0LF  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "srt = practices.sort_values('post_code').drop_duplicates('code', keep='first')\n",
    "srt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>addr_1</th>\n",
       "      <th>addr_2</th>\n",
       "      <th>borough</th>\n",
       "      <th>village</th>\n",
       "      <th>post_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B87016</td>\n",
       "      <td>213200001</td>\n",
       "      <td>MucoClear Sod Chlor 6% Inh Soln 4ml</td>\n",
       "      <td>1</td>\n",
       "      <td>38.94</td>\n",
       "      <td>36.06</td>\n",
       "      <td>60</td>\n",
       "      <td>B87016</td>\n",
       "      <td>WHITE ROSE SURGERY</td>\n",
       "      <td>WHITE ROSE SURGERY</td>\n",
       "      <td>EXCHANGE ST SOUTH ELMSALL</td>\n",
       "      <td>PONTEFRACT</td>\n",
       "      <td>WEST YORKSHIRE</td>\n",
       "      <td>WF9 2RD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B87016</td>\n",
       "      <td>0704020N0</td>\n",
       "      <td>Detrusitol_Tab 2mg</td>\n",
       "      <td>2</td>\n",
       "      <td>61.12</td>\n",
       "      <td>56.60</td>\n",
       "      <td>112</td>\n",
       "      <td>B87016</td>\n",
       "      <td>WHITE ROSE SURGERY</td>\n",
       "      <td>WHITE ROSE SURGERY</td>\n",
       "      <td>EXCHANGE ST SOUTH ELMSALL</td>\n",
       "      <td>PONTEFRACT</td>\n",
       "      <td>WEST YORKSHIRE</td>\n",
       "      <td>WF9 2RD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B87016</td>\n",
       "      <td>0604011G0</td>\n",
       "      <td>Elleste Solo_Tab 2mg</td>\n",
       "      <td>7</td>\n",
       "      <td>35.42</td>\n",
       "      <td>32.89</td>\n",
       "      <td>588</td>\n",
       "      <td>B87016</td>\n",
       "      <td>WHITE ROSE SURGERY</td>\n",
       "      <td>WHITE ROSE SURGERY</td>\n",
       "      <td>EXCHANGE ST SOUTH ELMSALL</td>\n",
       "      <td>PONTEFRACT</td>\n",
       "      <td>WEST YORKSHIRE</td>\n",
       "      <td>WF9 2RD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B87016</td>\n",
       "      <td>0407010F0</td>\n",
       "      <td>Co-Codamol Eff_Tab 30mg/500mg</td>\n",
       "      <td>16</td>\n",
       "      <td>169.54</td>\n",
       "      <td>158.06</td>\n",
       "      <td>1958</td>\n",
       "      <td>B87016</td>\n",
       "      <td>WHITE ROSE SURGERY</td>\n",
       "      <td>WHITE ROSE SURGERY</td>\n",
       "      <td>EXCHANGE ST SOUTH ELMSALL</td>\n",
       "      <td>PONTEFRACT</td>\n",
       "      <td>WEST YORKSHIRE</td>\n",
       "      <td>WF9 2RD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B87016</td>\n",
       "      <td>091000000</td>\n",
       "      <td>Centrum Advance 50+_Multivit/Mineral Tab</td>\n",
       "      <td>1</td>\n",
       "      <td>3.66</td>\n",
       "      <td>3.40</td>\n",
       "      <td>30</td>\n",
       "      <td>B87016</td>\n",
       "      <td>WHITE ROSE SURGERY</td>\n",
       "      <td>WHITE ROSE SURGERY</td>\n",
       "      <td>EXCHANGE ST SOUTH ELMSALL</td>\n",
       "      <td>PONTEFRACT</td>\n",
       "      <td>WEST YORKSHIRE</td>\n",
       "      <td>WF9 2RD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                                  bnf_name  items  \\\n",
       "0   B87016  213200001       MucoClear Sod Chlor 6% Inh Soln 4ml      1   \n",
       "1   B87016  0704020N0                        Detrusitol_Tab 2mg      2   \n",
       "2   B87016  0604011G0                      Elleste Solo_Tab 2mg      7   \n",
       "3   B87016  0407010F0             Co-Codamol Eff_Tab 30mg/500mg     16   \n",
       "4   B87016  091000000  Centrum Advance 50+_Multivit/Mineral Tab      1   \n",
       "\n",
       "      nic  act_cost  quantity    code                name              addr_1  \\\n",
       "0   38.94     36.06        60  B87016  WHITE ROSE SURGERY  WHITE ROSE SURGERY   \n",
       "1   61.12     56.60       112  B87016  WHITE ROSE SURGERY  WHITE ROSE SURGERY   \n",
       "2   35.42     32.89       588  B87016  WHITE ROSE SURGERY  WHITE ROSE SURGERY   \n",
       "3  169.54    158.06      1958  B87016  WHITE ROSE SURGERY  WHITE ROSE SURGERY   \n",
       "4    3.66      3.40        30  B87016  WHITE ROSE SURGERY  WHITE ROSE SURGERY   \n",
       "\n",
       "                      addr_2     borough         village post_code  \n",
       "0  EXCHANGE ST SOUTH ELMSALL  PONTEFRACT  WEST YORKSHIRE   WF9 2RD  \n",
       "1  EXCHANGE ST SOUTH ELMSALL  PONTEFRACT  WEST YORKSHIRE   WF9 2RD  \n",
       "2  EXCHANGE ST SOUTH ELMSALL  PONTEFRACT  WEST YORKSHIRE   WF9 2RD  \n",
       "3  EXCHANGE ST SOUTH ELMSALL  PONTEFRACT  WEST YORKSHIRE   WF9 2RD  \n",
       "4  EXCHANGE ST SOUTH ELMSALL  PONTEFRACT  WEST YORKSHIRE   WF9 2RD  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged = scripts.merge(srt, left_on='practice', right_on='code')\n",
    "merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>items</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>post_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">AL1 3HD</th>\n",
       "      <th>Acetic Acid_Ear Spy 2% 5ml</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ActivHeal 10cm x 10cm Non-Adh Foam Wound</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Adcal-D3_Capl 750mg/200u</th>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AgaMatrix Ultra-Thin Lancets 0.35mm/28 G</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Amiodarone HCl_Tab 200mg</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    items\n",
       "post_code bnf_name                                       \n",
       "AL1 3HD   Acetic Acid_Ear Spy 2% 5ml                    2\n",
       "          ActivHeal 10cm x 10cm Non-Adh Foam Wound      1\n",
       "          Adcal-D3_Capl 750mg/200u                     35\n",
       "          AgaMatrix Ultra-Thin Lancets 0.35mm/28 G      5\n",
       "          Amiodarone HCl_Tab 200mg                      5"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_items_by_postcode = merged.groupby(['post_code','bnf_name'])[['items']].sum()\n",
    "total_items_by_postcode.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>Acetic Acid_Ear Spy 2% 5ml</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>ActivHeal 10cm x 10cm Non-Adh Foam Wound</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>Adcal-D3_Capl 750mg/200u</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>AgaMatrix Ultra-Thin Lancets 0.35mm/28 G</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>Amiodarone HCl_Tab 200mg</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code                                  bnf_name  items\n",
       "0   AL1 3HD                Acetic Acid_Ear Spy 2% 5ml      2\n",
       "1   AL1 3HD  ActivHeal 10cm x 10cm Non-Adh Foam Wound      1\n",
       "2   AL1 3HD                  Adcal-D3_Capl 750mg/200u     35\n",
       "3   AL1 3HD  AgaMatrix Ultra-Thin Lancets 0.35mm/28 G      5\n",
       "4   AL1 3HD                  Amiodarone HCl_Tab 200mg      5"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_items_by_postcode.reset_index(inplace=True)\n",
    "total_items_by_postcode.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>items</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>post_code</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AL1 3HD</th>\n",
       "      <td>187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AL1 3JB</th>\n",
       "      <td>225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AL1 4JE</th>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AL10 0BS</th>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AL10 0LF</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           items\n",
       "post_code       \n",
       "AL1 3HD      187\n",
       "AL1 3JB      225\n",
       "AL1 4JE       70\n",
       "AL10 0BS     180\n",
       "AL10 0LF       1"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_items_max = total_items_by_postcode.groupby('post_code')[['items']].max()\n",
    "total_items_max.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL1 3JB</td>\n",
       "      <td>225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL1 4JE</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL10 0BS</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL10 0LF</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code  items\n",
       "0   AL1 3HD    187\n",
       "1   AL1 3JB    225\n",
       "2   AL1 4JE     70\n",
       "3  AL10 0BS    180\n",
       "4  AL10 0LF      1"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_items_max.reset_index(inplace=True)\n",
    "total_items_max.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_items = total_items_max.merge(total_items_by_postcode, on=['post_code','items'], how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items</th>\n",
       "      <th>bnf_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>187</td>\n",
       "      <td>Amoxicillin_Cap 500mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>187</td>\n",
       "      <td>Levothyrox Sod_Tab 25mcg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL1 3JB</td>\n",
       "      <td>225</td>\n",
       "      <td>Bendroflumethiazide_Tab 2.5mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL1 4JE</td>\n",
       "      <td>70</td>\n",
       "      <td>Aspirin_Tab 75mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL10 0BS</td>\n",
       "      <td>180</td>\n",
       "      <td>Amoxicillin_Cap 500mg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code  items                       bnf_name\n",
       "0   AL1 3HD    187          Amoxicillin_Cap 500mg\n",
       "1   AL1 3HD    187       Levothyrox Sod_Tab 25mcg\n",
       "2   AL1 3JB    225  Bendroflumethiazide_Tab 2.5mg\n",
       "3   AL1 4JE     70               Aspirin_Tab 75mg\n",
       "4  AL10 0BS    180          Amoxicillin_Cap 500mg"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_items.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items</th>\n",
       "      <th>bnf_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2558</th>\n",
       "      <td>GL53 9QU</td>\n",
       "      <td>4</td>\n",
       "      <td>3m Health Care_Cavilon Durable Barrier C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>B14 7AG</td>\n",
       "      <td>53</td>\n",
       "      <td>3m Health Care_Cavilon Durable Barrier C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>B24 9JN</td>\n",
       "      <td>162</td>\n",
       "      <td>3m Health Care_Cavilon Durable Barrier C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6439</th>\n",
       "      <td>SR4 7TP</td>\n",
       "      <td>1</td>\n",
       "      <td>3m Health Care_Cavilon No Sting 1ml Barr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7647</th>\n",
       "      <td>WN1 1NJ</td>\n",
       "      <td>2</td>\n",
       "      <td>3m Health Care_Cavilon No Sting 1ml Barr</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     post_code  items                                  bnf_name\n",
       "2558  GL53 9QU      4  3m Health Care_Cavilon Durable Barrier C\n",
       "67     B14 7AG     53  3m Health Care_Cavilon Durable Barrier C\n",
       "111    B24 9JN    162  3m Health Care_Cavilon Durable Barrier C\n",
       "6439   SR4 7TP      1  3m Health Care_Cavilon No Sting 1ml Barr\n",
       "7647   WN1 1NJ      2  3m Health Care_Cavilon No Sting 1ml Barr"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_items_sorted = total_items.sort_values('bnf_name').drop_duplicates(['post_code','items'], keep='first')\n",
    "total_items_sorted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items</th>\n",
       "      <th>bnf_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>187</td>\n",
       "      <td>Amoxicillin_Cap 500mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL1 3JB</td>\n",
       "      <td>225</td>\n",
       "      <td>Bendroflumethiazide_Tab 2.5mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL1 4JE</td>\n",
       "      <td>70</td>\n",
       "      <td>Aspirin_Tab 75mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL10 0BS</td>\n",
       "      <td>180</td>\n",
       "      <td>Amoxicillin_Cap 500mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AL10 0LF</td>\n",
       "      <td>1</td>\n",
       "      <td>ActiLymph Class 1 Combined Armsleeve + T</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code  items                                  bnf_name\n",
       "0   AL1 3HD    187                     Amoxicillin_Cap 500mg\n",
       "2   AL1 3JB    225             Bendroflumethiazide_Tab 2.5mg\n",
       "3   AL1 4JE     70                          Aspirin_Tab 75mg\n",
       "4  AL10 0BS    180                     Amoxicillin_Cap 500mg\n",
       "5  AL10 0LF      1  ActiLymph Class 1 Combined Armsleeve + T"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_items_sorted.sort_values('post_code', inplace=True)\n",
    "total_items_sorted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7572, 3)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_items_sorted.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>1822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL1 3JB</td>\n",
       "      <td>1778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL1 4JE</td>\n",
       "      <td>364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL10 0BS</td>\n",
       "      <td>1451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL10 0LF</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code  items\n",
       "0   AL1 3HD   1822\n",
       "1   AL1 3JB   1778\n",
       "2   AL1 4JE    364\n",
       "3  AL10 0BS   1451\n",
       "4  AL10 0LF      3"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = total_items_by_postcode.groupby('post_code')[['items']].sum().reset_index()\n",
    "s.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7572, 2)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items_x</th>\n",
       "      <th>items_y</th>\n",
       "      <th>bnf_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>1822</td>\n",
       "      <td>187</td>\n",
       "      <td>Amoxicillin_Cap 500mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL1 3JB</td>\n",
       "      <td>1778</td>\n",
       "      <td>225</td>\n",
       "      <td>Bendroflumethiazide_Tab 2.5mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL1 4JE</td>\n",
       "      <td>364</td>\n",
       "      <td>70</td>\n",
       "      <td>Aspirin_Tab 75mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL10 0BS</td>\n",
       "      <td>1451</td>\n",
       "      <td>180</td>\n",
       "      <td>Amoxicillin_Cap 500mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL10 0LF</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>ActiLymph Class 1 Combined Armsleeve + T</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code  items_x  items_y                                  bnf_name\n",
       "0   AL1 3HD     1822      187                     Amoxicillin_Cap 500mg\n",
       "1   AL1 3JB     1778      225             Bendroflumethiazide_Tab 2.5mg\n",
       "2   AL1 4JE      364       70                          Aspirin_Tab 75mg\n",
       "3  AL10 0BS     1451      180                     Amoxicillin_Cap 500mg\n",
       "4  AL10 0LF        3        1  ActiLymph Class 1 Combined Armsleeve + T"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df = s.merge(total_items_sorted, on='post_code')\n",
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.102634\n",
       "1    0.126547\n",
       "2    0.192308\n",
       "3    0.124052\n",
       "4    0.333333\n",
       "Name: amt%, dtype: float64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df['amt%'] = final_df['items_y'].astype(float)/final_df['items_x'].astype(float)\n",
    "final_df['amt%'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>items_x</th>\n",
       "      <th>items_y</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>amt%</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>1822</td>\n",
       "      <td>187</td>\n",
       "      <td>Amoxicillin_Cap 500mg</td>\n",
       "      <td>0.102634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL1 3JB</td>\n",
       "      <td>1778</td>\n",
       "      <td>225</td>\n",
       "      <td>Bendroflumethiazide_Tab 2.5mg</td>\n",
       "      <td>0.126547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL1 4JE</td>\n",
       "      <td>364</td>\n",
       "      <td>70</td>\n",
       "      <td>Aspirin_Tab 75mg</td>\n",
       "      <td>0.192308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL10 0BS</td>\n",
       "      <td>1451</td>\n",
       "      <td>180</td>\n",
       "      <td>Amoxicillin_Cap 500mg</td>\n",
       "      <td>0.124052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL10 0LF</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>ActiLymph Class 1 Combined Armsleeve + T</td>\n",
       "      <td>0.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code  items_x  items_y                                  bnf_name  \\\n",
       "0   AL1 3HD     1822      187                     Amoxicillin_Cap 500mg   \n",
       "1   AL1 3JB     1778      225             Bendroflumethiazide_Tab 2.5mg   \n",
       "2   AL1 4JE      364       70                          Aspirin_Tab 75mg   \n",
       "3  AL10 0BS     1451      180                     Amoxicillin_Cap 500mg   \n",
       "4  AL10 0LF        3        1  ActiLymph Class 1 Combined Armsleeve + T   \n",
       "\n",
       "       amt%  \n",
       "0  0.102634  \n",
       "1  0.126547  \n",
       "2  0.192308  \n",
       "3  0.124052  \n",
       "4  0.333333  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>post_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>amt%</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL1 3HD</td>\n",
       "      <td>Amoxicillin_Cap 500mg</td>\n",
       "      <td>0.102634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL1 3JB</td>\n",
       "      <td>Bendroflumethiazide_Tab 2.5mg</td>\n",
       "      <td>0.126547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL1 4JE</td>\n",
       "      <td>Aspirin_Tab 75mg</td>\n",
       "      <td>0.192308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL10 0BS</td>\n",
       "      <td>Amoxicillin_Cap 500mg</td>\n",
       "      <td>0.124052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AL10 0LF</td>\n",
       "      <td>ActiLymph Class 1 Combined Armsleeve + T</td>\n",
       "      <td>0.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  post_code                                  bnf_name      amt%\n",
       "0   AL1 3HD                     Amoxicillin_Cap 500mg  0.102634\n",
       "1   AL1 3JB             Bendroflumethiazide_Tab 2.5mg  0.126547\n",
       "2   AL1 4JE                          Aspirin_Tab 75mg  0.192308\n",
       "3  AL10 0BS                     Amoxicillin_Cap 500mg  0.124052\n",
       "4  AL10 0LF  ActiLymph Class 1 Combined Armsleeve + T  0.333333"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = final_df[['post_code','bnf_name','amt%']]\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = result.head(100)\n",
    "values = result.get_values().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "final=[]\n",
    "\n",
    "for item in values:\n",
    "    final.append(tuple(item))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "def items_by_region():\n",
    "    return final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('AL1 3HD', 'Amoxicillin_Cap 500mg', 0.1026344676180022), ('AL1 3JB', 'Bendroflumethiazide_Tab 2.5mg', 0.1265466816647919), ('AL1 4JE', 'Aspirin_Tab 75mg', 0.19230769230769232), ('AL10 0BS', 'Amoxicillin_Cap 500mg', 0.12405237767057202), ('AL10 0LF', 'ActiLymph Class 1 Combined Armsleeve + T', 0.3333333333333333), ('AL10 0NL', 'Amitriptyline HCl_Tab 10mg', 0.0639686684073107), ('AL10 0UR', 'Diazepam_Tab 10mg', 0.5434782608695652), ('AL10 8HP', 'Sertraline HCl_Tab 50mg', 0.10324129651860744), ('AL2 1ES', 'Levothyrox Sod_Tab 100mcg', 0.13074204946996468), ('AL2 3JX', 'Simvastatin_Tab 40mg', 0.0847231487658439), ('AL3 5ER', 'Bisoprolol Fumar_Tab 2.5mg', 0.11428571428571428), ('AL3 5HB', 'Omeprazole_Cap E/C 20mg', 0.16846758349705304), ('AL3 5JB', 'Alimemazine Tart_Tab 10mg', 1.0), ('AL3 5NF', 'Ramipril_Cap 10mg', 0.09449465899753492), ('AL3 5NP', 'Clopidogrel_Tab 75mg', 0.09023255813953489), ('AL3 7BL', 'Bendroflumethiazide_Tab 2.5mg', 0.08917197452229299), ('AL3 8LJ', 'Aspirin Disper_Tab 75mg', 0.17897727272727273), ('AL5 2BT', 'Bisoprolol Fumar_Tab 2.5mg', 0.137660485021398), ('AL5 4HX', 'Metformin HCl_Tab 500mg M/R', 0.07671601615074024), ('AL5 4QA', 'Lansoprazole_Cap 30mg (E/C Gran)', 0.14298480786416443), ('AL6 9EF', 'Atorvastatin_Tab 20mg', 0.17326732673267325), ('AL6 9SB', 'Mometasone Fur_Oint 0.1%', 0.2826086956521739), ('AL7 1BW', 'Irripod Sod Chlor Top Irrig 20ml', 0.1583710407239819), ('AL7 3UJ', 'Levothyrox Sod_Tab 50mcg', 0.13861386138613863), ('AL7 4HL', 'Clarithromycin_Tab 500mg', 0.07758094074526573), ('AL7 4PL', 'Levothyrox Sod_Tab 25mcg', 0.11315136476426799), ('AL8 6JL', 'Latanoprost_Eye Dps 50mcg/ml', 0.7142857142857143), ('AL8 7QG', 'Salbutamol_Inha 100mcg (200 D) CFF', 0.15814226925338037), ('AL9 7SN', 'Salbutamol_Inha 100mcg (200 D) CFF', 0.14134542705971279), ('B1 1EQ', 'Loperamide HCl_Cap 2mg', 0.5384615384615384), ('B1 3AL', 'Citalopram Hydrob_Tab 20mg', 0.11314475873544093), ('B1 3RA', 'Quetiapine_Tab 25mg', 0.21739130434782608), ('B10 0BS', 'Salbutamol_Inha 100mcg (200 D) CFF', 0.1784776902887139), ('B10 0JL', 'Desunin_Tab 800u', 0.17592592592592593), ('B10 0TU', 'Amlodipine_Tab 5mg', 0.228310502283105), ('B10 0UG', 'Amoxicillin_Cap 500mg', 0.10748299319727891), ('B10 9AB', 'Losartan Pot_Tab 50mg', 0.08932461873638345), ('B10 9QE', 'Fortisip Bottle_Liq (8 Flav)', 0.08923076923076922), ('B11 1LU', 'Paracet_Tab 500mg', 0.1488), ('B11 1TX', 'Fortisip Bottle_Liq (8 Flav)', 0.17955112219451372), ('B11 3ND', 'GlucoRx Nexus (Reagent)_Strips', 0.07524271844660194), ('B11 4AN', 'Metformin HCl_Tab 500mg', 0.16051502145922747), ('B11 4BW', 'Lansoprazole_Cap 30mg (E/C Gran)', 0.07043407043407043), ('B11 4DG', 'Paracet_Tab 500mg', 0.3543123543123543), ('B11 4RA', 'Paracet_Tab 500mg', 0.16339869281045752), ('B12 0UF', 'Lansoprazole_Cap 30mg (E/C Gran)', 0.1488833746898263), ('B12 0YA', 'Amoxicillin_Cap 500mg', 0.1375186846038864), ('B12 8HE', 'Atorvastatin_Tab 40mg', 0.19387755102040816), ('B12 8QE', 'Atorvastatin_Tab 20mg', 0.12996941896024464), ('B12 9LP', 'Aspirin Disper_Tab 75mg', 0.08866995073891626), ('B12 9RR', 'Aspirin Disper_Tab 75mg', 0.1111111111111111), ('B13 0HN', 'Amlodipine_Tab 5mg', 0.10548885077186965), ('B13 8JL', 'Nurse It Ster Dress Pack', 0.31699496106275765), ('B13 8JS', 'Salbutamol_Inha 100mcg (200 D) CFF', 0.15428571428571428), ('B13 8QS', 'Lansoprazole_Cap 15mg (E/C Gran)', 0.11512415349887133), ('B13 9HD', 'Influenza_Vac Inact 0.5ml Pfs', 0.5218037661050545), ('B13 9LH', 'Amlodipine_Tab 5mg', 0.23478260869565218), ('B14 4DU', 'Paracet_Tab 500mg', 0.18742985409652077), ('B14 4JU', 'Paracet_Tab 500mg', 0.1768465909090909), ('B14 5DJ', 'Atorvastatin_Tab 10mg', 0.10728476821192053), ('B14 5NG', 'Aspirin Disper_Tab 75mg', 0.1897810218978102), ('B14 5SB', 'Amlodipine_Tab 5mg', 0.16043956043956045), ('B14 6AA', 'Amlodipine_Tab 10mg', 0.05718954248366013), ('B14 7AG', '3m Health Care_Cavilon Durable Barrier C', 0.08466453674121406), ('B14 7NH', 'Omeprazole_Cap E/C 20mg', 0.12063492063492064), ('B15 1LZ', 'Levothyrox Sod_Tab 100mcg', 0.056847545219638244), ('B15 2QU', 'Salbutamol_Inha 100mcg (200 D) CFF', 0.10996563573883161), ('B15 3BU', 'Protopic_Oint 0.1%', 0.5952380952380952), ('B15 3SJ', 'Metronidazole_Tab 400mg', 1.0), ('B16 0HH', 'Lisinopril_Tab 5mg', 0.2079207920792079), ('B16 0HZ', 'Amoxicillin_Cap 500mg', 0.12021857923497267), ('B16 0LU', 'Paracet_Tab 500mg', 0.21238938053097345), ('B16 8HA', 'Aspirin Disper_Tab 75mg', 0.19321148825065274), ('B16 9AL', 'Aspirin Disper_Tab 75mg', 0.13713405238828968), ('B17 0HG', 'Omeprazole_Cap E/C 20mg', 0.13983050847457626), ('B17 8DP', 'Lansoprazole_Cap 30mg (E/C Gran)', 0.15562735595045774), ('B17 8LG', 'Stexerol-D3_Tab 1 000u', 0.17080745341614906), ('B17 9DB', 'Omeprazole_Cap E/C 20mg', 0.12826446280991735), ('B18 7AL', 'Aspirin Disper_Tab 75mg', 0.07208765859284891), ('B18 7BA', 'Citalopram Hydrob_Tab 20mg', 0.0877742946708464), ('B18 7EE', 'Metformin HCl_Tab 500mg', 0.3333333333333333), ('B19 1BP', 'Aspirin Disper_Tab 75mg', 0.14380321665089876), ('B19 1HL', 'Metformin HCl_Tab 500mg', 0.245136186770428), ('B19 1HS', 'Paracet_Tab 500mg', 0.2457757296466974), ('B19 1TT', 'Metformin HCl_Tab 500mg', 0.26259541984732826), ('B19 2JA', 'Amlodipine_Tab 5mg', 0.18029556650246306), ('B20 2BT', 'Simvastatin_Tab 20mg', 0.19021739130434784), ('B20 2ES', 'GlucoRx Lancets 0.31mm/30 Gauge', 0.07936507936507936), ('B20 2NR', 'Imuvac_Vac 0.5ml Pfs', 0.6362725450901804), ('B20 2QR', 'Bendroflumethiazide_Tab 2.5mg', 0.1571753986332574), ('B20 3HE', 'Simvastatin_Tab 20mg', 0.16216216216216217), ('B20 3QP', 'Ventolin_Evohaler 100mcg (200 D)', 0.18430034129692832), ('B21 0HL', 'Salbutamol_Inha 100mcg (200 D) CFF', 0.25), ('B21 0HR', 'Amlodipine_Tab 10mg', 0.16783216783216784), ('B21 9NH', 'Adcal-D3_Capl 750mg/200u', 0.17357222844344905), ('B21 9RY', 'Atorvastatin_Tab 10mg', 0.043362495245340436), ('B23 5BX', 'Lansoprazole_Cap 30mg (E/C Gran)', 0.12195121951219512), ('B23 5DD', 'Ventolin_Evohaler 100mcg (200 D)', 0.23908375089477452), ('B23 5TJ', 'Bendroflumethiazide_Tab 2.5mg', 0.1712962962962963), ('B23 6DJ', 'Lansoprazole_Cap 30mg (E/C Gran)', 0.11962931760741365)]\n"
     ]
    }
   ],
   "source": [
    "print items_by_region()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "def items_by_region():\n",
    "    [('AL1 3HD', u'Amoxicillin_Cap 500mg', 0.1026344676180022)] * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score('dw__items_by_region', items_by_region)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 4: script_anomalies\n",
    "\n",
    "Drug abuse is a source of human and monetary costs in health care. A first step in identifying practitioners that enable drug abuse is to look for practices where commonly abused drugs are prescribed unusually often. Let's try to find practices that prescribe an unusually high amount of opioids. The opioids we'll look for are given in the list below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "opioids = ['morphine', 'oxycodone', 'methadone', 'fentanyl', 'pethidine', 'buprenorphine', 'propoxyphene', 'codeine']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'morphine|oxycodone|methadone|fentanyl|pethidine|buprenorphine|propoxyphene|codeine'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioids_join = '|'.join(opioids)\n",
    "opioids_join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CHEM SUB</th>\n",
       "      <th>NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0101010A0</td>\n",
       "      <td>Alexitol Sodium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0101010B0</td>\n",
       "      <td>Almasilate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0101010C0</td>\n",
       "      <td>Aluminium Hydroxide</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0101010D0</td>\n",
       "      <td>Aluminium Hydroxide With Magnesium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0101010E0</td>\n",
       "      <td>Hydrotalcite</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    CHEM SUB                                NAME\n",
       "0  0101010A0                     Alexitol Sodium\n",
       "1  0101010B0                          Almasilate\n",
       "2  0101010C0                 Aluminium Hydroxide\n",
       "3  0101010D0  Aluminium Hydroxide With Magnesium\n",
       "4  0101010E0                        Hydrotalcite"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chem.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioid_codes = chem.loc[chem['NAME'].str.contains(opioids_join, case=False)]['CHEM SUB'].tolist()\n",
    "len(opioid_codes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>chem_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0101010A0</td>\n",
       "      <td>Alexitol Sodium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0101010B0</td>\n",
       "      <td>Almasilate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0101010C0</td>\n",
       "      <td>Aluminium Hydroxide</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0101010D0</td>\n",
       "      <td>Aluminium Hydroxide With Magnesium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0101010E0</td>\n",
       "      <td>Hydrotalcite</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    bnf_code                           chem_name\n",
       "0  0101010A0                     Alexitol Sodium\n",
       "1  0101010B0                          Almasilate\n",
       "2  0101010C0                 Aluminium Hydroxide\n",
       "3  0101010D0  Aluminium Hydroxide With Magnesium\n",
       "4  0101010E0                        Hydrotalcite"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chem_new = chem\n",
    "chem_new.columns = ['bnf_code','chem_name']\n",
    "chem_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B87016</td>\n",
       "      <td>213200001</td>\n",
       "      <td>MucoClear Sod Chlor 6% Inh Soln 4ml</td>\n",
       "      <td>1</td>\n",
       "      <td>38.94</td>\n",
       "      <td>36.06</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L85017</td>\n",
       "      <td>0503021C0</td>\n",
       "      <td>Aciclovir_Tab 800mg</td>\n",
       "      <td>4</td>\n",
       "      <td>13.44</td>\n",
       "      <td>12.49</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M81001</td>\n",
       "      <td>090900000</td>\n",
       "      <td>SMA_High Energy Milk Ready To Use</td>\n",
       "      <td>1</td>\n",
       "      <td>147.60</td>\n",
       "      <td>136.65</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F85063</td>\n",
       "      <td>0601011L0</td>\n",
       "      <td>Ins Humalog_100u/ml 10ml Vl</td>\n",
       "      <td>1</td>\n",
       "      <td>33.22</td>\n",
       "      <td>30.77</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B86667</td>\n",
       "      <td>1001010P0</td>\n",
       "      <td>Naproxen_Tab E/C 500mg</td>\n",
       "      <td>1</td>\n",
       "      <td>3.51</td>\n",
       "      <td>3.36</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                             bnf_name  items     nic  \\\n",
       "0   B87016  213200001  MucoClear Sod Chlor 6% Inh Soln 4ml      1   38.94   \n",
       "1   L85017  0503021C0                  Aciclovir_Tab 800mg      4   13.44   \n",
       "2   M81001  090900000    SMA_High Energy Milk Ready To Use      1  147.60   \n",
       "3   F85063  0601011L0          Ins Humalog_100u/ml 10ml Vl      1   33.22   \n",
       "4   B86667  1001010P0               Naproxen_Tab E/C 500mg      1    3.51   \n",
       "\n",
       "   act_cost  quantity  \n",
       "0     36.06        60  \n",
       "1     12.49       140  \n",
       "2    136.65     15000  \n",
       "3     30.77         2  \n",
       "4      3.36        28  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scripts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1001634, 7)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scripts.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B87016</td>\n",
       "      <td>213200001</td>\n",
       "      <td>MucoClear Sod Chlor 6% Inh Soln 4ml</td>\n",
       "      <td>1</td>\n",
       "      <td>38.94</td>\n",
       "      <td>36.06</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L85017</td>\n",
       "      <td>0503021C0</td>\n",
       "      <td>Aciclovir_Tab 800mg</td>\n",
       "      <td>4</td>\n",
       "      <td>13.44</td>\n",
       "      <td>12.49</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M81001</td>\n",
       "      <td>090900000</td>\n",
       "      <td>SMA_High Energy Milk Ready To Use</td>\n",
       "      <td>1</td>\n",
       "      <td>147.60</td>\n",
       "      <td>136.65</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F85063</td>\n",
       "      <td>0601011L0</td>\n",
       "      <td>Ins Humalog_100u/ml 10ml Vl</td>\n",
       "      <td>1</td>\n",
       "      <td>33.22</td>\n",
       "      <td>30.77</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B86667</td>\n",
       "      <td>1001010P0</td>\n",
       "      <td>Naproxen_Tab E/C 500mg</td>\n",
       "      <td>1</td>\n",
       "      <td>3.51</td>\n",
       "      <td>3.36</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                             bnf_name  items     nic  \\\n",
       "0   B87016  213200001  MucoClear Sod Chlor 6% Inh Soln 4ml      1   38.94   \n",
       "1   L85017  0503021C0                  Aciclovir_Tab 800mg      4   13.44   \n",
       "2   M81001  090900000    SMA_High Energy Milk Ready To Use      1  147.60   \n",
       "3   F85063  0601011L0          Ins Humalog_100u/ml 10ml Vl      1   33.22   \n",
       "4   B86667  1001010P0               Naproxen_Tab E/C 500mg      1    3.51   \n",
       "\n",
       "   act_cost  quantity  \n",
       "0     36.06        60  \n",
       "1     12.49       140  \n",
       "2    136.65     15000  \n",
       "3     30.77         2  \n",
       "4      3.36        28  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scrpt = scripts\n",
    "scrpt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "scrpt['opioid_prescription'] = scrpt['bnf_code'].isin(opioid_codes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1001634, 8)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scrpt.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "      <th>opioid_prescription</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B87016</td>\n",
       "      <td>213200001</td>\n",
       "      <td>MucoClear Sod Chlor 6% Inh Soln 4ml</td>\n",
       "      <td>1</td>\n",
       "      <td>38.94</td>\n",
       "      <td>36.06</td>\n",
       "      <td>60</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L85017</td>\n",
       "      <td>0503021C0</td>\n",
       "      <td>Aciclovir_Tab 800mg</td>\n",
       "      <td>4</td>\n",
       "      <td>13.44</td>\n",
       "      <td>12.49</td>\n",
       "      <td>140</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M81001</td>\n",
       "      <td>090900000</td>\n",
       "      <td>SMA_High Energy Milk Ready To Use</td>\n",
       "      <td>1</td>\n",
       "      <td>147.60</td>\n",
       "      <td>136.65</td>\n",
       "      <td>15000</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F85063</td>\n",
       "      <td>0601011L0</td>\n",
       "      <td>Ins Humalog_100u/ml 10ml Vl</td>\n",
       "      <td>1</td>\n",
       "      <td>33.22</td>\n",
       "      <td>30.77</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B86667</td>\n",
       "      <td>1001010P0</td>\n",
       "      <td>Naproxen_Tab E/C 500mg</td>\n",
       "      <td>1</td>\n",
       "      <td>3.51</td>\n",
       "      <td>3.36</td>\n",
       "      <td>28</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                             bnf_name  items     nic  \\\n",
       "0   B87016  213200001  MucoClear Sod Chlor 6% Inh Soln 4ml      1   38.94   \n",
       "1   L85017  0503021C0                  Aciclovir_Tab 800mg      4   13.44   \n",
       "2   M81001  090900000    SMA_High Energy Milk Ready To Use      1  147.60   \n",
       "3   F85063  0601011L0          Ins Humalog_100u/ml 10ml Vl      1   33.22   \n",
       "4   B86667  1001010P0               Naproxen_Tab E/C 500mg      1    3.51   \n",
       "\n",
       "   act_cost  quantity  opioid_prescription  \n",
       "0     36.06        60                False  \n",
       "1     12.49       140                False  \n",
       "2    136.65     15000                False  \n",
       "3     30.77         2                False  \n",
       "4      3.36        28                False  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scrpt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>addr_1</th>\n",
       "      <th>addr_2</th>\n",
       "      <th>borough</th>\n",
       "      <th>village</th>\n",
       "      <th>post_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "      <td>THE HEALTH CENTRE</td>\n",
       "      <td>LAWSON STREET</td>\n",
       "      <td>STOCKTON ON TEES</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS18 1HU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81002</td>\n",
       "      <td>QUEENS PARK MEDICAL CENTRE</td>\n",
       "      <td>QUEENS PARK MEDICAL CTR</td>\n",
       "      <td>FARRER STREET</td>\n",
       "      <td>STOCKTON ON TEES</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS18 2AW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81003</td>\n",
       "      <td>VICTORIA MEDICAL PRACTICE</td>\n",
       "      <td>THE HEALTH CENTRE</td>\n",
       "      <td>VICTORIA ROAD</td>\n",
       "      <td>HARTLEPOOL</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS26 8DB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81004</td>\n",
       "      <td>WOODLANDS ROAD SURGERY</td>\n",
       "      <td>6 WOODLANDS ROAD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MIDDLESBROUGH</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS1 3BE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81005</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "      <td>RECTORY LANE</td>\n",
       "      <td>GUISBOROUGH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TS14 7DJ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code                        name                   addr_1         addr_2  \\\n",
       "0  A81001         THE DENSHAM SURGERY        THE HEALTH CENTRE  LAWSON STREET   \n",
       "1  A81002  QUEENS PARK MEDICAL CENTRE  QUEENS PARK MEDICAL CTR  FARRER STREET   \n",
       "2  A81003   VICTORIA MEDICAL PRACTICE        THE HEALTH CENTRE  VICTORIA ROAD   \n",
       "3  A81004      WOODLANDS ROAD SURGERY         6 WOODLANDS ROAD            NaN   \n",
       "4  A81005          SPRINGWOOD SURGERY       SPRINGWOOD SURGERY   RECTORY LANE   \n",
       "\n",
       "            borough    village post_code  \n",
       "0  STOCKTON ON TEES  CLEVELAND  TS18 1HU  \n",
       "1  STOCKTON ON TEES  CLEVELAND  TS18 2AW  \n",
       "2        HARTLEPOOL  CLEVELAND  TS26 8DB  \n",
       "3     MIDDLESBROUGH  CLEVELAND   TS1 3BE  \n",
       "4       GUISBOROUGH        NaN  TS14 7DJ  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "practices.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81002</td>\n",
       "      <td>QUEENS PARK MEDICAL CENTRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81003</td>\n",
       "      <td>VICTORIA MEDICAL PRACTICE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81004</td>\n",
       "      <td>WOODLANDS ROAD SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81005</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice                        name\n",
       "0   A81001         THE DENSHAM SURGERY\n",
       "1   A81002  QUEENS PARK MEDICAL CENTRE\n",
       "2   A81003   VICTORIA MEDICAL PRACTICE\n",
       "3   A81004      WOODLANDS ROAD SURGERY\n",
       "4   A81005          SPRINGWOOD SURGERY"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pract = practices[['code', 'name']]\n",
    "pract.columns = ['practice', 'name']\n",
    "pract.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34597"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(scrpt.loc[scrpt['opioid_prescription']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81001    0.025424\n",
       "A81002    0.021834\n",
       "A81004    0.048780\n",
       "A81005    0.034965\n",
       "A81006    0.036269\n",
       "Name: frac, dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioids_per_practice = scrpt.groupby('practice')['opioid_prescription'].mean().rename('frac')\n",
    "opioids_per_practice.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81001    0.158080\n",
       "A81002    0.146462\n",
       "A81004    0.216069\n",
       "A81005    0.184337\n",
       "A81006    0.187446\n",
       "Name: frac_std, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioids_per_practice_std = scrpt.groupby('practice')['opioid_prescription'].std().rename('frac_std')\n",
    "opioids_per_practice_std.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.034540560723777348"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overall_rate = scrpt['opioid_prescription'].mean()\n",
    "overall_rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.18261309833034187"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overall_rate_std = scrpt['opioid_prescription'].std()\n",
    "overall_rate_std"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81001   -0.009117\n",
       "A81002   -0.012706\n",
       "A81004    0.014240\n",
       "A81005    0.000424\n",
       "A81006    0.001729\n",
       "Name: relative, dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "relative_opioids_per_practice = (opioids_per_practice - overall_rate).rename('relative')\n",
    "relative_opioids_per_practice.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81001    3.0\n",
       "A81002    5.0\n",
       "A81004    8.0\n",
       "A81005    5.0\n",
       "A81006    7.0\n",
       "Name: opioid, dtype: float64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioid = scrpt.groupby('practice')['opioid_prescription'].sum().rename('opioid')\n",
    "opioid.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81001    118\n",
       "A81002    229\n",
       "A81004    164\n",
       "A81005    143\n",
       "A81006    193\n",
       "Name: total, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total = scrpt.groupby('practice')['bnf_code'].count().rename('total')\n",
    "total.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81001    0.016811\n",
       "A81002    0.012067\n",
       "A81004    0.014260\n",
       "A81005    0.015271\n",
       "A81006    0.013145\n",
       "Name: std_err, dtype: float64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "standard_error_per_practice = (overall_rate_std/(total**0.5)).rename('std_err')\n",
    "standard_error_per_practice.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81001   -0.542317\n",
       "A81002   -1.052960\n",
       "A81004    0.998614\n",
       "A81005    0.027796\n",
       "A81006    0.131525\n",
       "Name: opioid_scores, dtype: float64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "opioid_scores = (relative_opioids_per_practice/standard_error_per_practice).rename('opioid_scores')\n",
    "opioid_scores.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged = pd.concat([opioid, total, opioids_per_practice, relative_opioids_per_practice, standard_error_per_practice,opioid_scores], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged = merged.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>opioid</th>\n",
       "      <th>total</th>\n",
       "      <th>frac</th>\n",
       "      <th>relative</th>\n",
       "      <th>std_err</th>\n",
       "      <th>opioid_scores</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>3.0</td>\n",
       "      <td>118</td>\n",
       "      <td>0.025424</td>\n",
       "      <td>-0.009117</td>\n",
       "      <td>0.016811</td>\n",
       "      <td>-0.542317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81002</td>\n",
       "      <td>5.0</td>\n",
       "      <td>229</td>\n",
       "      <td>0.021834</td>\n",
       "      <td>-0.012706</td>\n",
       "      <td>0.012067</td>\n",
       "      <td>-1.052960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81004</td>\n",
       "      <td>8.0</td>\n",
       "      <td>164</td>\n",
       "      <td>0.048780</td>\n",
       "      <td>0.014240</td>\n",
       "      <td>0.014260</td>\n",
       "      <td>0.998614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81005</td>\n",
       "      <td>5.0</td>\n",
       "      <td>143</td>\n",
       "      <td>0.034965</td>\n",
       "      <td>0.000424</td>\n",
       "      <td>0.015271</td>\n",
       "      <td>0.027796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81006</td>\n",
       "      <td>7.0</td>\n",
       "      <td>193</td>\n",
       "      <td>0.036269</td>\n",
       "      <td>0.001729</td>\n",
       "      <td>0.013145</td>\n",
       "      <td>0.131525</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice  opioid  total      frac  relative   std_err  opioid_scores\n",
       "0   A81001     3.0    118  0.025424 -0.009117  0.016811      -0.542317\n",
       "1   A81002     5.0    229  0.021834 -0.012706  0.012067      -1.052960\n",
       "2   A81004     8.0    164  0.048780  0.014240  0.014260       0.998614\n",
       "3   A81005     5.0    143  0.034965  0.000424  0.015271       0.027796\n",
       "4   A81006     7.0    193  0.036269  0.001729  0.013145       0.131525"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(merged)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9230, 6)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>practice</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>A81001</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>A81002</td>\n",
       "      <td>QUEENS PARK MEDICAL CENTRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>A81003</td>\n",
       "      <td>VICTORIA MEDICAL PRACTICE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>A81004</td>\n",
       "      <td>WOODLANDS ROAD SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>A81005</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index practice                        name\n",
       "0      0   A81001         THE DENSHAM SURGERY\n",
       "1      1   A81002  QUEENS PARK MEDICAL CENTRE\n",
       "2      2   A81003   VICTORIA MEDICAL PRACTICE\n",
       "3      3   A81004      WOODLANDS ROAD SURGERY\n",
       "4      4   A81005          SPRINGWOOD SURGERY"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pract.reset_index().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(pract)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = merged.merge(pract, on='practice', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>opioid</th>\n",
       "      <th>total</th>\n",
       "      <th>frac</th>\n",
       "      <th>relative</th>\n",
       "      <th>std_err</th>\n",
       "      <th>opioid_scores</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>3.0</td>\n",
       "      <td>118</td>\n",
       "      <td>0.025424</td>\n",
       "      <td>-0.009117</td>\n",
       "      <td>0.016811</td>\n",
       "      <td>-0.542317</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81001</td>\n",
       "      <td>3.0</td>\n",
       "      <td>118</td>\n",
       "      <td>0.025424</td>\n",
       "      <td>-0.009117</td>\n",
       "      <td>0.016811</td>\n",
       "      <td>-0.542317</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81002</td>\n",
       "      <td>5.0</td>\n",
       "      <td>229</td>\n",
       "      <td>0.021834</td>\n",
       "      <td>-0.012706</td>\n",
       "      <td>0.012067</td>\n",
       "      <td>-1.052960</td>\n",
       "      <td>QUEENS PARK MEDICAL CENTRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81004</td>\n",
       "      <td>8.0</td>\n",
       "      <td>164</td>\n",
       "      <td>0.048780</td>\n",
       "      <td>0.014240</td>\n",
       "      <td>0.014260</td>\n",
       "      <td>0.998614</td>\n",
       "      <td>WOODLANDS ROAD SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81004</td>\n",
       "      <td>8.0</td>\n",
       "      <td>164</td>\n",
       "      <td>0.048780</td>\n",
       "      <td>0.014240</td>\n",
       "      <td>0.014260</td>\n",
       "      <td>0.998614</td>\n",
       "      <td>BLUEBELL MEDICAL CENTRE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice  opioid  total      frac  relative   std_err  opioid_scores  \\\n",
       "0   A81001     3.0    118  0.025424 -0.009117  0.016811      -0.542317   \n",
       "1   A81001     3.0    118  0.025424 -0.009117  0.016811      -0.542317   \n",
       "2   A81002     5.0    229  0.021834 -0.012706  0.012067      -1.052960   \n",
       "3   A81004     8.0    164  0.048780  0.014240  0.014260       0.998614   \n",
       "4   A81004     8.0    164  0.048780  0.014240  0.014260       0.998614   \n",
       "\n",
       "                         name  \n",
       "0         THE DENSHAM SURGERY  \n",
       "1         THE DENSHAM SURGERY  \n",
       "2  QUEENS PARK MEDICAL CENTRE  \n",
       "3      WOODLANDS ROAD SURGERY  \n",
       "4     BLUEBELL MEDICAL CENTRE  "
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.sort_values('opioid_scores', ascending = False , inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>opioid</th>\n",
       "      <th>total</th>\n",
       "      <th>frac</th>\n",
       "      <th>relative</th>\n",
       "      <th>std_err</th>\n",
       "      <th>opioid_scores</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9644</th>\n",
       "      <td>Y04576</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.965459</td>\n",
       "      <td>0.091307</td>\n",
       "      <td>10.573825</td>\n",
       "      <td>ADDACTION NORTH DEVON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7795</th>\n",
       "      <td>P88636</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.965459</td>\n",
       "      <td>0.091307</td>\n",
       "      <td>10.573825</td>\n",
       "      <td>COMMUNITY DRUG TEAM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9645</th>\n",
       "      <td>Y04576</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.965459</td>\n",
       "      <td>0.091307</td>\n",
       "      <td>10.573825</td>\n",
       "      <td>ADDACTION NORTH DEVON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8718</th>\n",
       "      <td>Y02770</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.765459</td>\n",
       "      <td>0.081667</td>\n",
       "      <td>9.372928</td>\n",
       "      <td>NY HORIZONS HARROGATE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8332</th>\n",
       "      <td>Y01640</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.765459</td>\n",
       "      <td>0.081667</td>\n",
       "      <td>9.372928</td>\n",
       "      <td>SALFORD DRUG TEAM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     practice  opioid  total  frac  relative   std_err  opioid_scores  \\\n",
       "9644   Y04576     4.0      4   1.0  0.965459  0.091307      10.573825   \n",
       "7795   P88636     4.0      4   1.0  0.965459  0.091307      10.573825   \n",
       "9645   Y04576     4.0      4   1.0  0.965459  0.091307      10.573825   \n",
       "8718   Y02770     4.0      5   0.8  0.765459  0.081667       9.372928   \n",
       "8332   Y01640     4.0      5   0.8  0.765459  0.081667       9.372928   \n",
       "\n",
       "                       name  \n",
       "9644  ADDACTION NORTH DEVON  \n",
       "7795    COMMUNITY DRUG TEAM  \n",
       "9645  ADDACTION NORTH DEVON  \n",
       "8718  NY HORIZONS HARROGATE  \n",
       "8332      SALFORD DRUG TEAM  "
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>opioid</th>\n",
       "      <th>total</th>\n",
       "      <th>frac</th>\n",
       "      <th>relative</th>\n",
       "      <th>std_err</th>\n",
       "      <th>opioid_scores</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9644</th>\n",
       "      <td>Y04576</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.965459</td>\n",
       "      <td>0.091307</td>\n",
       "      <td>10.573825</td>\n",
       "      <td>ADDACTION NORTH DEVON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7795</th>\n",
       "      <td>P88636</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.965459</td>\n",
       "      <td>0.091307</td>\n",
       "      <td>10.573825</td>\n",
       "      <td>COMMUNITY DRUG TEAM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8718</th>\n",
       "      <td>Y02770</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.765459</td>\n",
       "      <td>0.081667</td>\n",
       "      <td>9.372928</td>\n",
       "      <td>NY HORIZONS HARROGATE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8332</th>\n",
       "      <td>Y01640</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.765459</td>\n",
       "      <td>0.081667</td>\n",
       "      <td>9.372928</td>\n",
       "      <td>SALFORD DRUG TEAM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9079</th>\n",
       "      <td>Y03738</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.965459</td>\n",
       "      <td>0.105432</td>\n",
       "      <td>9.157201</td>\n",
       "      <td>CRI DRUG SERVICE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     practice  opioid  total  frac  relative   std_err  opioid_scores  \\\n",
       "9644   Y04576     4.0      4   1.0  0.965459  0.091307      10.573825   \n",
       "7795   P88636     4.0      4   1.0  0.965459  0.091307      10.573825   \n",
       "8718   Y02770     4.0      5   0.8  0.765459  0.081667       9.372928   \n",
       "8332   Y01640     4.0      5   0.8  0.765459  0.081667       9.372928   \n",
       "9079   Y03738     3.0      3   1.0  0.965459  0.105432       9.157201   \n",
       "\n",
       "                       name  \n",
       "9644  ADDACTION NORTH DEVON  \n",
       "7795    COMMUNITY DRUG TEAM  \n",
       "8718  NY HORIZONS HARROGATE  \n",
       "8332      SALFORD DRUG TEAM  \n",
       "9079       CRI DRUG SERVICE  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final = final_df.drop_duplicates('name')\n",
    "final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>opioid_scores</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9644</th>\n",
       "      <td>ADDACTION NORTH DEVON</td>\n",
       "      <td>10.573825</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7795</th>\n",
       "      <td>COMMUNITY DRUG TEAM</td>\n",
       "      <td>10.573825</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8718</th>\n",
       "      <td>NY HORIZONS HARROGATE</td>\n",
       "      <td>9.372928</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8332</th>\n",
       "      <td>SALFORD DRUG TEAM</td>\n",
       "      <td>9.372928</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9079</th>\n",
       "      <td>CRI DRUG SERVICE</td>\n",
       "      <td>9.157201</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       name  opioid_scores  total\n",
       "9644  ADDACTION NORTH DEVON      10.573825      4\n",
       "7795    COMMUNITY DRUG TEAM      10.573825      4\n",
       "8718  NY HORIZONS HARROGATE       9.372928      5\n",
       "8332      SALFORD DRUG TEAM       9.372928      5\n",
       "9079       CRI DRUG SERVICE       9.157201      3"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = final[['name','opioid_scores','total']]\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = result.head(100)\n",
    "values = result.get_values().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "answer=[]\n",
    "\n",
    "for item in values:\n",
    "    answer.append(tuple(item))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "def script_anomalies():\n",
    "    return answer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('ADDACTION NORTH DEVON', 10.573824639125657, 4), ('COMMUNITY DRUG TEAM', 10.573824639125657, 4), ('NY HORIZONS HARROGATE', 9.3729275495027, 5), ('SALFORD DRUG TEAM', 9.3729275495027, 5), ('CRI DRUG SERVICE', 9.157200752644645, 3), ('CRI CAMDEN COMMUNITY DRUG TREATMENT SERV', 9.157200752644645, 3), ('WORCESTERSHIRE RECOVERY PARTNERSHIP', 9.157200752644645, 3), ('HAYFIELD GPWSI SERVICE', 9.157200752644645, 3), ('ADDICTIONS SERVICE', 9.145409115671335, 8), ('TURNING POINT CROYDON RECOVERY NETWORK', 8.479054497238709, 6), ('DUDLEY COMMUNITY PALLIATIVE MEDICINE', 8.060276069984052, 10), ('MACMILLAN ST HELENS SP  PALLIATIVE CARE', 7.835795414652864, 4), ('LIFELINE SOUTHWARK', 7.835795414652864, 4), ('STAR - EAST SUSSEX DRUG & ALCOHOL SERV', 7.835795414652864, 4), ('PLYMOUTH SPECIALIST ADDICTION SERVICE', 7.835795414652864, 4), (\"ST ANN'S HOSPICE/PAL CARE\", 7.835795414652864, 4), ('WESTMINSTER DAWS', 7.835795414652864, 4), ('COMMUNITY/SLH DAY CASE PALLIATIVE CARE', 7.778588563496554, 7), ('DR LOCAL CARE DIRECT OOH', 7.778588563496554, 7), ('LOCAL CARE DIRECT OOH', 7.778588563496554, 7), ('HACKNEY SUBSTANCE MISUSE SERVICE', 7.4768231054031515, 2), ('SOMERSET 999 GP CAR', 7.4768231054031515, 2), ('TURNING POINT TROWBRIDGE', 7.4768231054031515, 2), ('ADDACTION BRADFORD - BCSMS', 7.4768231054031515, 2), ('LIFELINE KIRKLEES', 7.4768231054031515, 2), ('HACKNEY RECOVERY SERVICE', 7.4768231054031515, 2), ('RBWM DAAT', 7.4768231054031515, 2), ('NMS CRI NOTTINGHAMSHIRE', 7.209330083959677, 8), ('LOROS', 6.923959761381819, 5), ('NRP SOUTH (NSFT)', 6.923959761381819, 5), ('BANES DOCTORS URGENT CARE (PAULTON)', 6.923959761381819, 5), ('LIFT SUBSTANCEMISUSE LEICESTERSHIRE', 6.923959761381819, 5), ('TURNING POINT LEICESTER & LEICESTERSHIRE', 6.923959761381819, 5), ('HEART OF KENT HOSPICE', 6.923959761381819, 5), (\"ST WILFRID'S HOSPICE\", 6.33700124944254, 15), ('COMPTON PALLIATIVE CARE TEAM', 6.243462996976479, 6), ('LUTON DRUG SERVICE', 6.243462996976479, 6), ('TRAFFORD DRUG SERVICE', 5.995596933047784, 3), ('MK SUBSTANCE MISUSE SERVICE', 5.995596933047784, 3), ('RECOVERY CENTRE NEWTON AYCLIFFE', 5.995596933047784, 3), ('TURNING POINT SOMERSET', 5.995596933047784, 3), ('ST OSWALDS PALLIATIVE CARE', 5.995596933047784, 3), ('CRIME REDUCTION INITIATIVES', 5.995596933047784, 3), ('PROJECT ANSWER', 5.995596933047784, 3), ('GRANTHAM MINOR INJURIES AND ILLNESS UNIT', 5.995596933047784, 3), ('SWANSWELL SUBSTANCEMISUSE LEICESTERSHIRE', 5.995596933047784, 3), ('BOWTHORPE CARE VILLAGE - NPL', 5.995596933047784, 3), ('PALLIATIVE CARE', 5.995596933047784, 3), ('NORTH LANCASHIRE INSPIRE', 5.995596933047784, 3), ('SUFFOLK RECOVERY SERVICE - LOWESTOFT', 5.995596933047784, 3), ('CRI ASPIRE', 5.995596933047784, 3), ('EAST CHESHIRE SUBSTANCE MISUSE SERVICE', 5.995596933047784, 3), ('ST CUTHBERTS HOSPICE', 5.977048242001399, 11), ('HUMBER NHS FT - BEVERLEY LOCALITY TEAM', 5.708833017673109, 7), ('WHEATFIELDS SRC HOSPICE', 5.708833017673109, 7), ('HUMBER NHS FT - POCK & GOOLE LOC TEAM', 5.393165324296798, 13), ('CHCP - POCK & GOOLE LOC TEAM', 5.393165324296798, 13), ('HOSPICE IN THE WEALD', 5.286912319562829, 1), ('SPECIALIST NURSE TEAM PARKINSON', 5.286912319562829, 1), ('HARDWICK GP ACUTE VISIT', 5.286912319562829, 1), ('ADDACTION NORTH SOMERSET', 5.286912319562829, 1), ('DR IRIS', 5.286912319562829, 1), ('COMM HOSP - OAK WARD', 5.286912319562829, 1), ('SPRINGHILL HOSPICE', 5.286912319562829, 1), ('KNOWSLEY INTEGRATED RECOVERY SERV (CRI)', 5.286912319562829, 1), ('PALLIATIVE CARE SERVICE', 5.286912319562829, 1), ('NEW HOPE', 5.286912319562829, 1), ('ST CLARE HOSPICE', 5.286912319562829, 1), ('PALLIATIVE MEDICINE', 5.286912319562829, 1), ('IDAS', 5.286912319562829, 1), ('BRANCHING OUT', 5.286912319562829, 1), (\"ST MARGARET'S HOSPICE\", 5.286912319562829, 1), ('COMMUNITY MIDWIFE SERVICES', 5.286912319562829, 1), ('CRI WIRRAL', 5.286912319562829, 1), ('ADDACTION', 5.286912319562829, 1), ('ADDACTION THURROCK', 5.286912319562829, 1), ('SOUTHEND TREATMENT AND RECOVERY SERVICE', 5.286912319562829, 1), ('BOLTON COMMUNITY DRUG AND ALCOHOL SERV', 5.286912319562829, 1), ('LIFELINE REDCAR & CLEVELAND', 5.286912319562829, 1), (\"ISIS WOMEN'S SERVICE\", 5.286912319562829, 1), ('THE BEACON', 5.286912319562829, 1), ('SHARED CARE DRUG SERVICE', 5.286912319562829, 1), ('TURNING POINT CHIPPENHAM', 5.286912319562829, 1), ('LPS THE SURGERY', 5.286912319562829, 1), ('ALCOHOL SERVICES FOR THE COMMUNITY', 5.286912319562829, 1), ('CRI BROMLEYCOMMUNITY DRUGS PROJECT', 5.286912319562829, 1), ('WOKING HOSPICE', 5.286912319562829, 1), ('NORTH BRADFORD DRUG SERVICE', 5.286912319562829, 1), ('GRANTHAM ADDACTION', 5.286912319562829, 1), ('SMART DRUG & ALCOHOL SERVICES WOKINGHAM', 5.286912319562829, 1), ('CALDERDALE COMMUNITY DERMATOLOGY SERVICE', 5.286912319562829, 1), ('TURNING POINT MEDWAY', 5.286912319562829, 1), ('COMMUNITY MEDICAL TEAM - EAST ELMBRIDGE', 5.286912319562829, 1), ('EAST RIDING PARTNERSHIP', 5.286912319562829, 1), ('BARNET RECOVERY CENTRE FINCHLEY WDP', 5.286912319562829, 1), ('CRI-NEWHAM RISE', 5.286912319562829, 1), ('E.R.C.J.P. SERVICE (EAST)', 5.286912319562829, 1), ('ADDACTION SUBSTANCE MISUSE SERVICE', 5.286912319562829, 1), ('MCAS', 5.286912319562829, 1), ('JOHN TAYLOR HOSPICE', 5.27325105224802, 8)]\n"
     ]
    }
   ],
   "source": [
    "print script_anomalies()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These are generic names for drugs, not brand names. Generic drug names can be found using the `'bnf_code'` field in `scripts` along with the `chem` table.. Use the list of opioids provided above along with these fields to make a new field in the `scripts` data that flags whether the row corresponds with a opioid prescription."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "scripts_chem = pd.merge(scripts, chem, left_on='bnf_code', right_on='CHEM SUB')\n",
    "scripts_chem.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "Owing to helpful comments and answers, I managed to partially resolve problems. Here are my intermediate results...\n",
    "\n",
    "Number of opioids in chem dataframe is __35__. Number of opioid prescriptions in scripts dataframe is __34597__. There is __9230__ practices in total, to be analyzed. The mean value of opioid prescription rate for the whole population is 0.0345405607238. From individual practices means, I subtracted the total mean to get relative prescription rate. Standard deviation (std) for the whole population (based on binomial distribution) is 0.18261309833. For each practice I determined standard error by dividing standard deviation with the square root of total prescriptions, including opioid and non-opioid ones. Finally, I determined opioid scores by dividing relative prescription rate with standard error. Floats were used everywhere, to avoid accidental integer division."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now for each practice calculate the proportion of its prescriptions containing opioids.\n",
    "\n",
    "**Hint:** Consider the following list: `[0, 1, 1, 0, 0, 0]`. What proportion of the entries are 1s? What is the mean value?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "opioids_per_practice = ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How do these proportions compare to the overall opioid prescription rate? Subtract off the proportion of all prescriptions that are opioids from each practice's proportion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "relative_opioids_per_practice = ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we know the difference between each practice's opioid prescription rate and the overall rate, we can identify which practices prescribe opioids at above average or below average rates. However, are the differences from the overall rate important or just random deviations? In other words, are the differences from the overall rate big or small?\n",
    "\n",
    "To answer this question we have to quantify the difference we would typically expect between a given practice's opioid prescription rate and the overall rate. This quantity is called the **standard error**, and is related to the **standard deviation**, $\\sigma$. The standard error in this case is\n",
    "\n",
    "$$ \\frac{\\sigma}{\\sqrt{n}} $$\n",
    "\n",
    "where $n$ is the number of prescriptions each practice made. Calculate the standard error for each practice. Then divide `relative_opioids_per_practice` by the standard errors. We'll call the final result `opioid_scores`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "standard_error_per_practice = ...\n",
    "opioid_scores = ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The quantity we have calculated in `opioid_scores` is called a **z-score**:\n",
    "\n",
    "$$ \\frac{\\bar{X} - \\mu}{\\sqrt{\\sigma^2/n}} $$\n",
    "\n",
    "Here $\\bar{X}$ corresponds with the proportion for each practice, $\\mu$ corresponds with the proportion across all practices, $\\sigma^2$ corresponds with the variance of the proportion across all practices, and $n$ is the number of prescriptions made by each practice. Notice $\\bar{X}$ and $n$ will be different for each practice, while $\\mu$ and $\\sigma$ are determined across all prescriptions, and so are the same for every z-score. The z-score is a useful statistical tool used for hypothesis testing, finding outliers, and comparing data about different types of objects or events.\n",
    "\n",
    "Now that we've calculated this statistic, take the 100 practices with the largest z-score. Return your result as a list of tuples in the form `(practice_name, z-score, number_of_scripts)`. Sort your tuples by z-score in descending order. Note that some practice codes will correspond with multiple names. In this case, use the first match when sorting names alphabetically."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_practices = ...\n",
    "anomalies = ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "def script_anomalies():\n",
    "    return [(\"ADDACTION NORTH DEVON\", 10.5738246391, 4.0)] * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  0.94\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score('dw__script_anomalies', script_anomalies)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 5: script_growth\n",
    "\n",
    "Another way to identify anomalies is by comparing current data to historical data. In the case of identifying sites of drug abuse, we might compare a practice's current rate of opioid prescription to their rate 5 or 10 years ago. Unless the nature of the practice has changed, the profile of drugs they prescribe should be relatively stable. We might also want to identify trends through time for business reasons, identifying drugs that are gaining market share. That's what we'll do in this question.\n",
    "\n",
    "We'll load in beneficiary data from 6 months earlier, June 2016, and calculate the percent growth in prescription rate from June 2016 to January 2017 for each `bnf_name`.  Normalize the percent growth in prescriptions of individual items by the percent change in total number of prescriptions (think about whether this normalization should be a division or a subtraction). We'll return the 50 items with largest growth and the 50 items with the largest shrinkage (i.e. negative percent growth) as a list of tuples sorted by growth rate in descending order in the format `(script_name, growth_rate, raw_2016_count)`. You'll notice that many of the 50 fastest growing items have low counts of prescriptions in 2016. Filter out any items that were prescribed less than 50 times."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>count_16</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>365 Film 4cm x 5cm VP Adh Film Dress</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>365 Non Adherent 10cm x 10cm Pfa Plas Fa</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>365 Non Adherent 5cm x 5cm Pfa Plas Face</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>365 Non Woven Island 10cm x 20cm Adh Dre</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>365 Non Woven Island 5cm x 7.2cm Adh Dre</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   bnf_name  count_16\n",
       "0      365 Film 4cm x 5cm VP Adh Film Dress         1\n",
       "1  365 Non Adherent 10cm x 10cm Pfa Plas Fa         1\n",
       "2  365 Non Adherent 5cm x 5cm Pfa Plas Face         2\n",
       "3  365 Non Woven Island 10cm x 20cm Adh Dre         1\n",
       "4  365 Non Woven Island 5cm x 7.2cm Adh Dre         1"
      ]
     },
     "execution_count": 206,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drugs_16=scripts_16[['bnf_name', 'items']]\n",
    "drugs_16=drugs_16.groupby('bnf_name').count().reset_index().drop_duplicates()\n",
    "drugs_16.columns=[['bnf_name', 'count_16']]\n",
    "drugs_16.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>count_17</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>365 Film 4cm x 5cm VP Adh Film Dress</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>365 IV Transpt IV 10cm x 12cm VP Adh Fil</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>365 Non Adherent 10cm x 10cm Pfa Plas Fa</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>365 Non Woven Island 10cm x 10cm Adh Dre</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3M Micropore Silicone 2.5cm x 5m Surg Ad</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   bnf_name  count_17\n",
       "0      365 Film 4cm x 5cm VP Adh Film Dress         1\n",
       "1  365 IV Transpt IV 10cm x 12cm VP Adh Fil         1\n",
       "2  365 Non Adherent 10cm x 10cm Pfa Plas Fa         1\n",
       "3  365 Non Woven Island 10cm x 10cm Adh Dre         2\n",
       "4  3M Micropore Silicone 2.5cm x 5m Surg Ad        14"
      ]
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drugs_17=scripts[['bnf_name', 'items']]\n",
    "drugs_17=drugs_17.groupby('bnf_name').count().reset_index().drop_duplicates()\n",
    "drugs_17.columns=[['bnf_name', 'count_17']]\n",
    "drugs_17.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_16=drugs_16['count_16'].sum()\n",
    "total_17=drugs_17['count_17'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {},
   "outputs": [],
   "source": [
    "drugs_16['rate_16']=drugs_16['count_16']/total_16.astype(float)\n",
    "drugs_17['rate_17']=drugs_17['count_17']/total_17.astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>count_16</th>\n",
       "      <th>rate_16</th>\n",
       "      <th>count_17</th>\n",
       "      <th>rate_17</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>365 Film 4cm x 5cm VP Adh Film Dress</td>\n",
       "      <td>1</td>\n",
       "      <td>9.765577e-07</td>\n",
       "      <td>1</td>\n",
       "      <td>9.983687e-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>365 Non Adherent 10cm x 10cm Pfa Plas Fa</td>\n",
       "      <td>1</td>\n",
       "      <td>9.765577e-07</td>\n",
       "      <td>1</td>\n",
       "      <td>9.983687e-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3m Health Care_Cavilon Durable Barrier C</td>\n",
       "      <td>905</td>\n",
       "      <td>8.837847e-04</td>\n",
       "      <td>913</td>\n",
       "      <td>9.115106e-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3m Health Care_Cavilon No Sting 1ml Barr</td>\n",
       "      <td>228</td>\n",
       "      <td>2.226552e-04</td>\n",
       "      <td>209</td>\n",
       "      <td>2.086591e-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3m Health Care_Cavilon No Sting 3ml Barr</td>\n",
       "      <td>103</td>\n",
       "      <td>1.005854e-04</td>\n",
       "      <td>88</td>\n",
       "      <td>8.785644e-05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   bnf_name  count_16       rate_16  count_17  \\\n",
       "0      365 Film 4cm x 5cm VP Adh Film Dress         1  9.765577e-07         1   \n",
       "1  365 Non Adherent 10cm x 10cm Pfa Plas Fa         1  9.765577e-07         1   \n",
       "2  3m Health Care_Cavilon Durable Barrier C       905  8.837847e-04       913   \n",
       "3  3m Health Care_Cavilon No Sting 1ml Barr       228  2.226552e-04       209   \n",
       "4  3m Health Care_Cavilon No Sting 3ml Barr       103  1.005854e-04        88   \n",
       "\n",
       "        rate_17  \n",
       "0  9.983687e-07  \n",
       "1  9.983687e-07  \n",
       "2  9.115106e-04  \n",
       "3  2.086591e-04  \n",
       "4  8.785644e-05  "
      ]
     },
     "execution_count": 210,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drugs=drugs_16.merge(drugs_17, on='bnf_name', how='inner').drop_duplicates('bnf_name')\n",
    "drugs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>count_16</th>\n",
       "      <th>rate_16</th>\n",
       "      <th>count_17</th>\n",
       "      <th>rate_17</th>\n",
       "      <th>growth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>365 Film 4cm x 5cm VP Adh Film Dress</td>\n",
       "      <td>1</td>\n",
       "      <td>9.765577e-07</td>\n",
       "      <td>1</td>\n",
       "      <td>9.983687e-07</td>\n",
       "      <td>0.022335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>365 Non Adherent 10cm x 10cm Pfa Plas Fa</td>\n",
       "      <td>1</td>\n",
       "      <td>9.765577e-07</td>\n",
       "      <td>1</td>\n",
       "      <td>9.983687e-07</td>\n",
       "      <td>0.022335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3m Health Care_Cavilon Durable Barrier C</td>\n",
       "      <td>905</td>\n",
       "      <td>8.837847e-04</td>\n",
       "      <td>913</td>\n",
       "      <td>9.115106e-04</td>\n",
       "      <td>0.031372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3m Health Care_Cavilon No Sting 1ml Barr</td>\n",
       "      <td>228</td>\n",
       "      <td>2.226552e-04</td>\n",
       "      <td>209</td>\n",
       "      <td>2.086591e-04</td>\n",
       "      <td>-0.062860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3m Health Care_Cavilon No Sting 3ml Barr</td>\n",
       "      <td>103</td>\n",
       "      <td>1.005854e-04</td>\n",
       "      <td>88</td>\n",
       "      <td>8.785644e-05</td>\n",
       "      <td>-0.126549</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   bnf_name  count_16       rate_16  count_17  \\\n",
       "0      365 Film 4cm x 5cm VP Adh Film Dress         1  9.765577e-07         1   \n",
       "1  365 Non Adherent 10cm x 10cm Pfa Plas Fa         1  9.765577e-07         1   \n",
       "2  3m Health Care_Cavilon Durable Barrier C       905  8.837847e-04       913   \n",
       "3  3m Health Care_Cavilon No Sting 1ml Barr       228  2.226552e-04       209   \n",
       "4  3m Health Care_Cavilon No Sting 3ml Barr       103  1.005854e-04        88   \n",
       "\n",
       "        rate_17    growth  \n",
       "0  9.983687e-07  0.022335  \n",
       "1  9.983687e-07  0.022335  \n",
       "2  9.115106e-04  0.031372  \n",
       "3  2.086591e-04 -0.062860  \n",
       "4  8.785644e-05 -0.126549  "
      ]
     },
     "execution_count": 211,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drugs['growth']=(drugs['rate_17']-drugs['rate_16'])/drugs['rate_16'].astype(float)\n",
    "drugs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.021846573014780202"
      ]
     },
     "execution_count": 212,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_growth=(total_17-total_16)/total_16.astype(float) #-0.0218465730148\n",
    "total_growth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>rel_growth</th>\n",
       "      <th>count_16</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3m Health Care_Cavilon Durable Barrier C</td>\n",
       "      <td>0.009525</td>\n",
       "      <td>905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3m Health Care_Cavilon No Sting 1ml Barr</td>\n",
       "      <td>-0.084707</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3m Health Care_Cavilon No Sting 3ml Barr</td>\n",
       "      <td>-0.148396</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3m Health Care_Cavilon No Sting Barrier</td>\n",
       "      <td>-0.152489</td>\n",
       "      <td>548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A.S Saliva Orthana Spy 50ml (App)</td>\n",
       "      <td>0.176040</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   bnf_name  rel_growth  count_16\n",
       "2  3m Health Care_Cavilon Durable Barrier C    0.009525       905\n",
       "3  3m Health Care_Cavilon No Sting 1ml Barr   -0.084707       228\n",
       "4  3m Health Care_Cavilon No Sting 3ml Barr   -0.148396       103\n",
       "5   3m Health Care_Cavilon No Sting Barrier   -0.152489       548\n",
       "8         A.S Saliva Orthana Spy 50ml (App)    0.176040        99"
      ]
     },
     "execution_count": 213,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drugs['rel_growth']=drugs['growth']+total_growth\n",
    "drugs=drugs[(drugs['count_16']>50)]\n",
    "drugs = drugs[['bnf_name', 'rel_growth', 'count_16']]\n",
    "drugs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>rel_growth</th>\n",
       "      <th>count_16</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1483</th>\n",
       "      <td>Butec_Transdermal Patch 10mcg/hr</td>\n",
       "      <td>3.946340</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1485</th>\n",
       "      <td>Butec_Transdermal Patch 5mcg/hr</td>\n",
       "      <td>2.672189</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4483</th>\n",
       "      <td>Fostair NEXThaler_Inh 200mcg/6mcg (120D)</td>\n",
       "      <td>1.726507</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3673</th>\n",
       "      <td>Dulaglutide_Inj 1.5mg/0.5ml Pf Dev</td>\n",
       "      <td>1.363601</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Abasaglar KwikPen_100u/ml 3ml Pf Pen</td>\n",
       "      <td>1.283053</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      bnf_name  rel_growth  count_16\n",
       "1483          Butec_Transdermal Patch 10mcg/hr    3.946340        57\n",
       "1485           Butec_Transdermal Patch 5mcg/hr    2.672189        75\n",
       "4483  Fostair NEXThaler_Inh 200mcg/6mcg (120D)    1.726507        77\n",
       "3673        Dulaglutide_Inj 1.5mg/0.5ml Pf Dev    1.363601        78\n",
       "30        Abasaglar KwikPen_100u/ml 3ml Pf Pen    1.283053        55"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drugs.sort_values('rel_growth', ascending=False, inplace=True)\n",
    "drugs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>rel_growth</th>\n",
       "      <th>count_16</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1483</th>\n",
       "      <td>Butec_Transdermal Patch 10mcg/hr</td>\n",
       "      <td>3.946340</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1485</th>\n",
       "      <td>Butec_Transdermal Patch 5mcg/hr</td>\n",
       "      <td>2.672189</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4483</th>\n",
       "      <td>Fostair NEXThaler_Inh 200mcg/6mcg (120D)</td>\n",
       "      <td>1.726507</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3673</th>\n",
       "      <td>Dulaglutide_Inj 1.5mg/0.5ml Pf Dev</td>\n",
       "      <td>1.363601</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Abasaglar KwikPen_100u/ml 3ml Pf Pen</td>\n",
       "      <td>1.283053</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      bnf_name  rel_growth  count_16\n",
       "1483          Butec_Transdermal Patch 10mcg/hr    3.946340        57\n",
       "1485           Butec_Transdermal Patch 5mcg/hr    2.672189        75\n",
       "4483  Fostair NEXThaler_Inh 200mcg/6mcg (120D)    1.726507        77\n",
       "3673        Dulaglutide_Inj 1.5mg/0.5ml Pf Dev    1.363601        78\n",
       "30        Abasaglar KwikPen_100u/ml 3ml Pf Pen    1.283053        55"
      ]
     },
     "execution_count": 215,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final = pd.concat([drugs.iloc[0:50], drugs.iloc[len(drugs)-50: len(drugs)]], axis=0)\n",
    "final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['Butec_Transdermal Patch 10mcg/hr', 3.946340409455863, 57],\n",
       "       ['Butec_Transdermal Patch 5mcg/hr', 2.672188773229457, 75],\n",
       "       ['Fostair NEXThaler_Inh 200mcg/6mcg (120D)', 1.7265072272651727, 77],\n",
       "       ['Dulaglutide_Inj 1.5mg/0.5ml Pf Dev', 1.363600606294229, 78],\n",
       "       ['Abasaglar KwikPen_100u/ml 3ml Pf Pen', 1.2830530392006783, 55]], dtype=object)"
      ]
     },
     "execution_count": 216,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arr = final.values\n",
    "arr[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {},
   "outputs": [],
   "source": [
    "lst=[]\n",
    "\n",
    "for item in arr:\n",
    "    lst.append(tuple(item))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {},
   "outputs": [],
   "source": [
    "def script_growth():\n",
    "    return lst"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score('dw__script_growth', script_growth)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 6: rare_scripts\n",
    "\n",
    "Does a practice's prescription costs originate from routine care or from reliance on rarely prescribed treatments? Commonplace treatments can carry lower costs than rare treatments because of efficiencies in large-scale production. While some specialist practices can't help but avoid prescribing rare medicines because there are no alternatives, some practices may be prescribing a unnecessary amount of brand-name products when generics are available. Let's identify practices whose costs disproportionately originate from rarely prescribed items.\n",
    "\n",
    "First we have to identify which `'bnf_code'` are rare. To do this, find the probability $p$ of a prescription having a particular `'bnf_code'` if the `'bnf_code'` was randomly chosen from the unique options in the beneficiary data. We will call a `'bnf_code'` rare if it is prescribed at a rate less than $0.1p$."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "p = ...\n",
    "rates = ...\n",
    "rare_codes = ...\n",
    "scripts['rare'] = ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculating probability for each bnf_code\n",
    "p = 1 / float(scripts['bnf_code'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>count_prescription</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0101010C0</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0101010F0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0101010G0</td>\n",
       "      <td>364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0101010I0</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0101010J0</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    bnf_code  count_prescription\n",
       "0  0101010C0                  30\n",
       "1  0101010F0                   3\n",
       "2  0101010G0                 364\n",
       "3  0101010I0                  21\n",
       "4  0101010J0                  38"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculating rare prescription i.e ratio of count of bnf_code per prescription and total count\n",
    "\n",
    "rates = scripts.groupby('bnf_code')['bnf_code'].count().rename('count_prescription').reset_index()\n",
    "rates.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1001634.0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_count = scripts['bnf_code'].count().astype(float)\n",
    "total_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.000030\n",
       "1    0.000003\n",
       "2    0.000363\n",
       "3    0.000021\n",
       "4    0.000038\n",
       "Name: count_prescription, dtype: float64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rare = rates['count_prescription'] / total_count\n",
    "rare.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['0101010C0', '0101010F0', '0101010I0', '0101010J0', '0101010N0']"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Filtering the records on the basis of  rate < 0.1p\n",
    "\n",
    "rare_codes =  rates[rare < 0.1*p]['bnf_code'].tolist()\n",
    "rare_codes[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating 'rare' column in scripts\n",
    "scrpt = scripts\n",
    "scrpt['rare'] = scrpt['bnf_code'].isin(rare_codes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>practice</th>\n",
       "      <th>bnf_code</th>\n",
       "      <th>bnf_name</th>\n",
       "      <th>items</th>\n",
       "      <th>nic</th>\n",
       "      <th>act_cost</th>\n",
       "      <th>quantity</th>\n",
       "      <th>rare</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B87016</td>\n",
       "      <td>213200001</td>\n",
       "      <td>MucoClear Sod Chlor 6% Inh Soln 4ml</td>\n",
       "      <td>1</td>\n",
       "      <td>38.94</td>\n",
       "      <td>36.06</td>\n",
       "      <td>60</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L85017</td>\n",
       "      <td>0503021C0</td>\n",
       "      <td>Aciclovir_Tab 800mg</td>\n",
       "      <td>4</td>\n",
       "      <td>13.44</td>\n",
       "      <td>12.49</td>\n",
       "      <td>140</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M81001</td>\n",
       "      <td>090900000</td>\n",
       "      <td>SMA_High Energy Milk Ready To Use</td>\n",
       "      <td>1</td>\n",
       "      <td>147.60</td>\n",
       "      <td>136.65</td>\n",
       "      <td>15000</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F85063</td>\n",
       "      <td>0601011L0</td>\n",
       "      <td>Ins Humalog_100u/ml 10ml Vl</td>\n",
       "      <td>1</td>\n",
       "      <td>33.22</td>\n",
       "      <td>30.77</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B86667</td>\n",
       "      <td>1001010P0</td>\n",
       "      <td>Naproxen_Tab E/C 500mg</td>\n",
       "      <td>1</td>\n",
       "      <td>3.51</td>\n",
       "      <td>3.36</td>\n",
       "      <td>28</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  practice   bnf_code                             bnf_name  items     nic  \\\n",
       "0   B87016  213200001  MucoClear Sod Chlor 6% Inh Soln 4ml      1   38.94   \n",
       "1   L85017  0503021C0                  Aciclovir_Tab 800mg      4   13.44   \n",
       "2   M81001  090900000    SMA_High Energy Milk Ready To Use      1  147.60   \n",
       "3   F85063  0601011L0          Ins Humalog_100u/ml 10ml Vl      1   33.22   \n",
       "4   B86667  1001010P0               Naproxen_Tab E/C 500mg      1    3.51   \n",
       "\n",
       "   act_cost  quantity   rare  \n",
       "0     36.06        60  False  \n",
       "1     12.49       140  False  \n",
       "2    136.65     15000  False  \n",
       "3     30.77         2  False  \n",
       "4      3.36        28  False  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scrpt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81001     5447.55\n",
       "A81002    42759.63\n",
       "A81004    11804.14\n",
       "A81005     9746.88\n",
       "A81006    24645.30\n",
       "Name: act_cost, dtype: float64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate total sum of act_cost for all treatment per prescription\n",
    "treatment_sum = scrpt.groupby('practice')['act_cost'].sum()\n",
    "treatment_sum.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "practice\n",
       "A81001     51.84\n",
       "A81002    237.75\n",
       "A81004      8.64\n",
       "A81005    463.32\n",
       "A81006     68.48\n",
       "Name: act_cost, dtype: float64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate cost for rare treatment per prescription\n",
    "rare_treatment_sum = scrpt[scrpt['rare']].groupby('practice')['act_cost'].sum()\n",
    "rare_treatment_sum.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.01554553433731762, 0.0678173558942156)"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate proportion of costs that originate from rare treatment i.e ratio of rare_treatment_sum and treatment_sum\n",
    "\n",
    "rare_cost_prop = (rare_treatment_sum / treatment_sum)\n",
    "rare_cost_prop.fillna(0, inplace=True)\n",
    "\n",
    "mean_val = scrpt[scrpt['rare']]['act_cost'].sum() / scrpt['act_cost'].sum()\n",
    "\n",
    "relative_rare_cost_prop = rare_cost_prop - mean_val \n",
    "standard_errors = relative_rare_cost_prop.std()\n",
    "\n",
    "mean_val, standard_errors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>z-score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>-0.088905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81002</td>\n",
       "      <td>-0.147239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81004</td>\n",
       "      <td>-0.218434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81005</td>\n",
       "      <td>0.471703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81006</td>\n",
       "      <td>-0.188254</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code   z-score\n",
       "0  A81001 -0.088905\n",
       "1  A81002 -0.147239\n",
       "2  A81004 -0.218434\n",
       "3  A81005  0.471703\n",
       "4  A81006 -0.188254"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rare_scores = (relative_rare_cost_prop / standard_errors).rename('z-score').reset_index()\n",
    "rare_scores.columns =['code','z-score']\n",
    "rare_scores.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>z-score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8034</th>\n",
       "      <td>Y03106</td>\n",
       "      <td>14.516261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8760</th>\n",
       "      <td>Y04676</td>\n",
       "      <td>14.516261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7441</th>\n",
       "      <td>Y00799</td>\n",
       "      <td>14.516261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8403</th>\n",
       "      <td>Y03976</td>\n",
       "      <td>14.516261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7600</th>\n",
       "      <td>Y01731</td>\n",
       "      <td>14.516261</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        code    z-score\n",
       "8034  Y03106  14.516261\n",
       "8760  Y04676  14.516261\n",
       "7441  Y00799  14.516261\n",
       "8403  Y03976  14.516261\n",
       "7600  Y01731  14.516261"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sorting rare_score in descendig order of z-score\n",
    "rare_scores.sort_values('z-score', ascending=False, inplace=True)\n",
    "rare_scores.columns =['code','z-score']\n",
    "rare_scores.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>addr_1</th>\n",
       "      <th>addr_2</th>\n",
       "      <th>borough</th>\n",
       "      <th>village</th>\n",
       "      <th>post_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "      <td>THE HEALTH CENTRE</td>\n",
       "      <td>LAWSON STREET</td>\n",
       "      <td>STOCKTON ON TEES</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS18 1HU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81002</td>\n",
       "      <td>QUEENS PARK MEDICAL CENTRE</td>\n",
       "      <td>QUEENS PARK MEDICAL CTR</td>\n",
       "      <td>FARRER STREET</td>\n",
       "      <td>STOCKTON ON TEES</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS18 2AW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81003</td>\n",
       "      <td>VICTORIA MEDICAL PRACTICE</td>\n",
       "      <td>THE HEALTH CENTRE</td>\n",
       "      <td>VICTORIA ROAD</td>\n",
       "      <td>HARTLEPOOL</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS26 8DB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81004</td>\n",
       "      <td>WOODLANDS ROAD SURGERY</td>\n",
       "      <td>6 WOODLANDS ROAD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MIDDLESBROUGH</td>\n",
       "      <td>CLEVELAND</td>\n",
       "      <td>TS1 3BE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81005</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "      <td>RECTORY LANE</td>\n",
       "      <td>GUISBOROUGH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TS14 7DJ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code                        name                   addr_1         addr_2  \\\n",
       "0  A81001         THE DENSHAM SURGERY        THE HEALTH CENTRE  LAWSON STREET   \n",
       "1  A81002  QUEENS PARK MEDICAL CENTRE  QUEENS PARK MEDICAL CTR  FARRER STREET   \n",
       "2  A81003   VICTORIA MEDICAL PRACTICE        THE HEALTH CENTRE  VICTORIA ROAD   \n",
       "3  A81004      WOODLANDS ROAD SURGERY         6 WOODLANDS ROAD            NaN   \n",
       "4  A81005          SPRINGWOOD SURGERY       SPRINGWOOD SURGERY   RECTORY LANE   \n",
       "\n",
       "            borough    village post_code  \n",
       "0  STOCKTON ON TEES  CLEVELAND  TS18 1HU  \n",
       "1  STOCKTON ON TEES  CLEVELAND  TS18 2AW  \n",
       "2        HARTLEPOOL  CLEVELAND  TS26 8DB  \n",
       "3     MIDDLESBROUGH  CLEVELAND   TS1 3BE  \n",
       "4       GUISBOROUGH        NaN  TS14 7DJ  "
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "practices.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81002</td>\n",
       "      <td>QUEENS PARK MEDICAL CENTRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81003</td>\n",
       "      <td>VICTORIA MEDICAL PRACTICE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81004</td>\n",
       "      <td>WOODLANDS ROAD SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81005</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code                        name\n",
       "0  A81001         THE DENSHAM SURGERY\n",
       "1  A81002  QUEENS PARK MEDICAL CENTRE\n",
       "2  A81003   VICTORIA MEDICAL PRACTICE\n",
       "3  A81004      WOODLANDS ROAD SURGERY\n",
       "4  A81005          SPRINGWOOD SURGERY"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pract = practices[['code','name']].drop_duplicates('code')\n",
    "pract.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10843, 2)"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pract.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9230, 2)"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rare_scores.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>z-score</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A81001</td>\n",
       "      <td>-0.088905</td>\n",
       "      <td>THE DENSHAM SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A81002</td>\n",
       "      <td>-0.147239</td>\n",
       "      <td>QUEENS PARK MEDICAL CENTRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A81004</td>\n",
       "      <td>-0.218434</td>\n",
       "      <td>WOODLANDS ROAD SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A81005</td>\n",
       "      <td>0.471703</td>\n",
       "      <td>SPRINGWOOD SURGERY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A81006</td>\n",
       "      <td>-0.188254</td>\n",
       "      <td>TENNANT STREET MEDICAL PRACTICE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code   z-score                             name\n",
       "0  A81001 -0.088905              THE DENSHAM SURGERY\n",
       "1  A81002 -0.147239       QUEENS PARK MEDICAL CENTRE\n",
       "2  A81004 -0.218434           WOODLANDS ROAD SURGERY\n",
       "3  A81005  0.471703               SPRINGWOOD SURGERY\n",
       "4  A81006 -0.188254  TENNANT STREET MEDICAL PRACTICE"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Joining rare_scores with practices to get (practice, name, z-score)\n",
    "joined_data = pd.merge(rare_scores, pract, on='code', how ='inner')#.drop_duplicates(['code','name'])\n",
    "joined_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9230, 3)"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_data.sort_values('z-score', ascending=False, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_data.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "required_cols = ['code', 'name', 'z-score']\n",
    "final_df = joined_data[required_cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>z-score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Y03106</td>\n",
       "      <td>CRI GPWSI</td>\n",
       "      <td>14.516261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Y04704</td>\n",
       "      <td>PCOC RESPIRATORY SERVICE</td>\n",
       "      <td>14.516261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Y00215</td>\n",
       "      <td>ORTHOPAEDIC &amp; RHEUMATOLOGY</td>\n",
       "      <td>14.516261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Y03268</td>\n",
       "      <td>CLECKHEATON DERMATOLOGY GPSI</td>\n",
       "      <td>14.516261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Y03572</td>\n",
       "      <td>DR WRIGHT ED CLINIC (BELMONT)</td>\n",
       "      <td>14.516261</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code                           name    z-score\n",
       "0  Y03106                      CRI GPWSI  14.516261\n",
       "1  Y04704       PCOC RESPIRATORY SERVICE  14.516261\n",
       "2  Y00215     ORTHOPAEDIC & RHEUMATOLOGY  14.516261\n",
       "3  Y03268   CLECKHEATON DERMATOLOGY GPSI  14.516261\n",
       "4  Y03572  DR WRIGHT ED CLINIC (BELMONT)  14.516261"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9230, 3)"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = final_df.head(100)\n",
    "arr = final_df.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "lst=[]\n",
    "\n",
    "for item in arr:\n",
    "    lst.append(tuple(item))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rare_scripts():\n",
    "    return lst"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rare_scripts():\n",
    "    #return [(\"Y03106\", \"CRI GPWSI\", 14.516)] * 100\n",
    "    return [tuple(i) for i in final_df.values][:100]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now for each practice, calculate the proportion of costs that originate from prescription of rare treatments (i.e. rare `'bnf_code'`). Use the `'act_cost'` field for this calculation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rare_cost_prop = ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will calculate a z-score for each practice based on this proportion.\n",
    "First take the difference of `rare_cost_prop` and the proportion of costs originating from rare treatments across all practices."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "relative_rare_cost_prop = ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will estimate the standard errors (i.e. the denominator of the z-score) by simply taking the standard deviation of this difference."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "standard_errors = ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally compute the z-scores. Return the practices with the top 100 z-scores in the form `(practice_name, proportion)`. Note that some practice codes will correspond with multiple names. In this case, use the first match when sorting names alphabetically."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rare_scores = ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rare_scripts():\n",
    "    return [(\"Y03106\", \"CRI GPWSI\", 14.516)] * 100\n",
    "    rare_scores.sort_values('act_cost', ascending=False, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  0.96\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score('dw__rare_scripts', rare_scripts)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Copyright &copy; 2017 The Data Incubator.  All rights reserved.*"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "nbclean": true
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
